Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Workday And Network Day Formula Challenge


I am trying to create a sheet for a project that will identify dates a project will conclude omitting weekends and holidays. I keep getting an error with the formula I have.

In my spreadsheet:
A1 is the start date
B1 is the number of days after the start date.

The formula I am using:
=WORKDAY(A1,NETWORKDAYS(A1,A1+B1),{""4/6/2007","5/28/2007", "7/4/2007","9/3/2007","11/22/2007","12/25/2007","1/1/2008","1/21/2008","3/21/2008","5/26/2008", "7/4/2008","9/1/2008","11/27/2008","12/25/2008"}"}+0)

The error I get is with the parens (A1,A1+B1). - at least the parens are highlighted in purple.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Force Formulae Recalculation & Return Day Name From Workday Function
I'm trying to make a schedule of deposits made for the month of March 2008. (Deposits are made on a daily basis.) With this, I wanted to know when the deposit would clear with the bank using the WORKDAY formula given that I have to count 6 banking days after the date of deposit (Saturdays and Sundays excluded).

I'm using Mac and I recently upgraded to Office 2008 (never tried WORKDAY formula in other versions). I am aware of the syntax used for this formula and it works just fine in other cases. But I noticed that with start_dates falling on a Thursday or Friday of the week, Excel would give me a result date that falls on a Sunday, which is odd given that it's supposed to ignore non-working days or weekends. (I haven't even gotten to inputting holidays yet.)

View Replies!   View Related
Formula Challenge: OFFSET Application
i have a rather tricky formual challenge.

I present the triangulated data below, years-by-years....

View Replies!   View Related
Workday Formula On A Conditional Formatting
I am having an issue, because aparently workday formula does not work in conditional formattings.
I have the two formulas below to use in conditional formattings, but I can't figure out how to re-write them so that Excel will allow me.

View Replies!   View Related
Formula That Would Calculate What Workday A Date Would Be
I am trying to find a formula that would calculate what workday a date would be.

So if the date is 2/19/2007, I want to know that is the 13th workday.

View Replies!   View Related
WORKDAY Formula Replacement W/o Use Of Analysis Add-In
My boss has made it clear I can't require our people to use the add-in.

I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?

Using the add-in, this is what I have:

myCell.FormulaR1C1 = "=WORKDAY(RC[1],-RC[2])"

View Replies!   View Related
Workday Function/Formula Ignoring Weekends
I'm at my wits end trying to make a workday formula work, but am having no luck. I've searched the other posts, but did not quite find a match. I'm trying to subtract a number of days from a workday function, but it seems to bypass ignoring the weekends (as is supposed to be done automatically with Workday). Here's the formula I'm trying to use:

=WORKDAY(H5,L5,' Reference Info'!$E$110:$E$117)-(I5/8)

I5/8 is a column with hours - this just calculates a number of days to subtract based on the number of scheduled hours.

The formula will calculate, but it still ignores weekends. I've tried burying the I5/8 within the parenthesis, moving it around in the formula,

View Replies!   View Related
Math Challenge: A Button To Sort Numbers
I need code to attach to a button!

In cell A1 I enter number of teams.
In Cell A2 I enter number of games to play.
Is there a way for me to click a button and...
In columns A and B it will give me a list of teams to play each other
based on the criteria above.
In Column A will be home team and B is Visitor.

Example I enter 9 teams with 12 games to play, hit the button and it will list the games in columns A and B.

View Replies!   View Related
Macro Challenge-Copy Range Of Non Empty Cells From 2 Sheets
first time posting. Need some help. I have a workbook with two sheets - (say sheet A and sheet B) that I need to copy a range from and paste (concatonated) into a new workbook. The thing is I want to copy the "non-empty" cells in columns d and e of each sheet - another words - I dont want to specify a specific cell range because users will be periodically adding new rows. Then I want to paste the "combined" from both sheets into a new sheet in a new workbook.


View Replies!   View Related
Day Accrual Formula
I need to make a formula that will accrue vacation days automatically.

We accrue days on the 15th of the month( If you start on February 15th, you have accrued the days for February).

If the person has been here for 3 years they accrue 15 days a year.

If they've been here for 7 years, they accrue 23 days.

If they've been here for 10 years they accrue 30 days a year.

All days are accrued evenly over the 12 months.

For example If someone started 3 years ago on March 3rd, starting March of 2007, they will be accruing 23/12 days a month, but for January and February, they will accrue 15/12 days a month.

The formula must work off their start date.

View Replies!   View Related
Displaying Day As A Formula
I'm working with a worksheet with one sheet per day of the month (we use a master, copy/paste, change the copy to the current month, and go from there). I've already got it to figure the dates based off (really difficult).

View Replies!   View Related
Formula To Subtract A Number Per Day
I needa formular that will subtract a number per day, for example as one day passes you can set the amount of number to be taken away from the total.

View Replies!   View Related
Formula: Day Into Month+year
i need to put a date into a month +year
like this:
1/06/2008jun/08

i did this with a format date.
After that i tried to copy/paste special values. So i can make i pivot table on the jun/08. But the problem is he still see it like a date.

MaandTotal
jun/081jun/0810

he sees that 1/6/2008 is not the same as 2/6/2008
that is why i got 2 lines of jun

but i just want one and that he counts all the junes together


View Replies!   View Related
Formula To Return Day Of Year
Is there a function that will return the day of any particular year?

For example, if the date 01/01/2008 is entered, it will return 1 and if 31/12/1977 is entered, it will return 365?

View Replies!   View Related
Formula For Total Figure Each Day
I need a single formula that will give me a total figure each day.

I am trying to get a total weight for each days sales

Sheet 1 holds the unique ID and total items sold for each day
Sheet 2 holds the unique ID and the weight of each item.

I have got as far as creating helper cells but this is making the whole sheet way too big. I know there is a more efficient way .....

View Replies!   View Related
Formula- Previous Working Day
Yesterday (13/03/2007) was the 9th working day of the month (basing a week on Mon – Fri) – today is the 10th working day

Is there any formula, or mix of formula’s I can use to automatically calculate this?

View Replies!   View Related
Last Working Day Of Month Formula
I am trying to create a formula to work out the last working day of each month, but I can't come up with anything that is less than the 1,024 formula limit. I am using a UK calendar so the only two holidays to effect the last working day of the month will be Easter and the last Monday in May.

This is the formula I am using which takes account of Easter, the checking for the last Monday in May is simple enough in another column, but I would rather keep it in one column, just wondering whether there is a simpler way of doing this.

A1 is a specified date and then I would like to work out the last working day of the month, on a new row, for up to the next 50 years....

View Replies!   View Related
Formula To List The First And The Last Day Of Each Month
is there a formula to list the first and the last day of each month and for every year?.

example:
if i put in cell A1: 01/09/06 ----->01/September/2006

the result should be:
A2: 30/09/06 ------> 30/September/2006
A3: 01/10/06
A4: 31/10/06
A5: 01/11/06
A6: 30/11/06
A7: 01/12/06
A8: 31/12/06
A9: 01/01/07
A10: 31/01/07
A11: 01/02/07
A12: 28/02/07
.
.
.
and so on

how to do that with a formula?

View Replies!   View Related
Formula Needed That Changes According To A Specific Day
I'm trying to make a spreadsheet for work so when im not there they can just enter the day for example sunday and what they have and then it will show them what they need to prep

View Replies!   View Related
Displaying A Certain Day Of The Week Formula
Is there a way to create a date formula that will only display the Friday date with in any given week?

View Replies!   View Related
Formula To Display The Last Day Of The Previous Month
I am using the following formula to show the first day of previous 6 month cycle.

=DATE(YEAR(TODAY()),MONTH(TODAY())-6,1)

That formula would display: 07/01/2009

I want to have another formula show the last day of the previous month.

Basically it would display: 12/31/2009

View Replies!   View Related
CreatE A Formula That Would Total A Rolling 90 Day
I am trying to do is create a total of only the last 90 days of entries from a list of $ amounts. Attached is an example. We are going to start keeping track of our cashiers variances but at the top of the sheet we only want to know what their last 90 days of entries equal.

This would be considered a rolling 90 days, so older variances drop off after 90 days, they would still be left on screen but would not effect the main total.

View Replies!   View Related
Formula To Find Out The Date And Day Difference
Can you share the formula to find out the date and day difference. Foe example ( from :Thu 06-Jul-06 11:59 AM TO Mon 10-Jul-06 11:56 AM)

I have the dates is the same formate as shown in example.

View Replies!   View Related
Day Of Week -day Of Month List Auto Setup
What I'm after is a sheet that self generates the day of the week in column A and the day of the month in column B. I have a month long sheet where daily entries get made in the DOW row, the day of the month is a reference. I have a macro to generate a new sheet for the next month and would like to auto populate the DOW and DOM. This typically gets done on the second day of the month (data from the first day is entered on the second)

View Replies!   View Related
Macro Or Formula That Will Sum On 1st Day Of The MOnth
after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following

View Replies!   View Related
Relating A Cell's Day To A Cells Day.
I am trying to relate a specific cells day to a date.
for example:

Today's date is : 11 May 2006
Therefore the day would be: Monday.

I want to link the day cell to the date cell, so when the date changes so does the day.

View Replies!   View Related
Formula Columns Where My Workers Record The Amounts Of Work They Do During The Day
I have three columns where my workers record the amounts of work they do during the day. The columns are labelled as follows:

Column A – “Correspondences’’
Column B – “Linking’’
Column C – “Allocating”

When a task has been completed an “X’’ is put in the relevant column (which is then summed at the bottom). I use these sums to calculate each workers productivity in the following way – number of “x’’ divided by number of days worked. This is a simple formula, however, each “x” now equates to time – which is causing me problems. An “x’’ is Column A equals 1 hour, Column B 1 hour and Column C 2 hours.

I am looking for someone to help me create a formula which will calculate all possibilities in the above situation above. Please bear in mind that a worker can do one of these tasks during the course of a week, two or all three .

View Replies!   View Related
Calendar From Formula :: Nth Day Of Week For A Month And Year
I'm using this formula from

[url]

Nth Day Of Week For A Month And Year

This formula will return will return the date of Nth day-of-week for a given month and year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998. Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.

=DATE(Yr,Mon,1+((Nth-(DoW>=WEEKDAY(DATE(Yr,Mon,1))))*7)+
(DoW-WEEKDAY(DATE(Yr,Mon,1))))

Where Yr, Mon, Nth, and DoW are cell references or values indicating Year, Month, Nth, and Day-Of-Week.

I would like to be able to change the year and month in A2 and B2 and have the calendar change.

I will be inserting rows between the weeks to return appts, if I can get this part working.

I could make a new tab for each month, but I thought I would give this a try....

View Replies!   View Related
Calculating If A Period Is During A Workday
I have two dates in this format:

start: 17.07.2009 11:00:00
end: 17.07.2009 14:00:00

i need to check if the period in between those is during a workday.

our workday starts at 10:00:00 and finishes at 19:00:00

so my expected result is 3 hours. (1400-1100)

other example:

start: 17.07.2009 11:00:00
end: 18.07.2009 11:00:00

result is 1900-1100 + 1100-1000

= 9 hours

because it is during two workdays...

is there a way to calculate this in VBA?

View Replies!   View Related
WorkDay Function VBA
The function is to show the next workday date in cell a1


View Replies!   View Related
Workday Function Or Similar
This is probably a simple question, but one I can't figure out after several hours. I am creating a spreadsheet focused on certifications.

I need to take the date each initial certification was achieved, and add x years to each cert, which is simple enough.

The sticky part is that the (date + x years) can't logically be a weekend; if it is, I need to subtract one or two days from the result, so that it's the *weekday* immediately prior.

e.g. if my initial certification date is: 1/23/2006 and my certifcation expires in four 4 years:

A47= date(2006,1,23)
h47= 10

=DATE(YEAR(A47)+H47,MONTH(A47),DAY(A47))

Result = Saturday, January 23, 2010

If the result is a Saturday or Sunday, I need to subtract one or two days from the date. In this example, I'd like: Friday, January 22, 2010 as the result.

I've been playing with 'if', et. al., to no avail.

View Replies!   View Related
Convert Date To Workday
If I have a date in cell G6 of 10/7/08, how could I get the workday of 5 in cell H6? I tried the workday function, but have been unsucessful so far.

View Replies!   View Related
Conditional Format If Not Workday
Maybe I'm getting a blank, maybe the answer is so easy, but lately I can´t solve this: How do I use conditional format to change cell color if cell value (date) is not workday?

View Replies!   View Related
Macro For Workday Dates
Im trying to display dates in excel sheet using macros. can anybody help me how to show Workday(x,x) in VBA.

View Replies!   View Related
Workday Function Use In VBA
I have a spreadsheet setup that uses the workday function. Shown as follows: =Workday(P$2-1,V2,Z2:Z$11)

P2 is a set date in which the location will not change. Z:Z is a list of Holidays. V2 should offset by 1 row. I want it when a command button is hit it runs that formula untill the rows value = "End". I have found "Working with Workdays" information at MSDN's website http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbadev/html/workingwithworkdays.asp It seems to be for access but I'm trying to use in Excel and I am getting errors/confused. The main error I'm getting is Compile Error: Method or data member not found because of rst.FindFirst.

View Replies!   View Related
Working Day Function: (Day + Next 1st Working Day)
I was looking for a final result as follows

21-Aug-09 + 1 = 24-Aug-09 (Day + next 1st working day)

21-Aug-09 + 3 = 26-Aug-09 (Day + next 3rd working day)

View Replies!   View Related
Automatically Add One Day To An Existing Day
how can i automatically add one day to an existing day..
a
1 Monday

in b1 what would be the formula for tuesday..

=a1+1

?

View Replies!   View Related
Array Formula: List The Number Of A Specific Class For A Particular Month (any Day)
I'm having difficulty creating an array formula. In a multi-column sheet, I am looking at a column with classes and a column with a date (in the format 7-Oct-09). I need to list the number of a specific class for a particular month (any day). I have tried the following which gives only the number of classes:
=COUNTIF(A4:A2500,"AA")+COUNTIF(H4:H2500,"10/??/09") and
=SUM((A4:A2500="AA")*(H4:H2500="??-Oct-??")) which gives me 0. Maybe an array formula is not the way to do this.

View Replies!   View Related
Count Formula- Spreadsheet That Is Being Used For Tracking Work Completed Each Day In A Week
I have a spreadsheet that is being used for tracking work completed each day in a week. Each day has 5 columns and 10 rows of data to potentially be entered, some of the data is text and some is numerical. Can anyone help me come up with a formula that will count the text and the numerical entries for each day? I tried to use a nested if statement but it exceeded the number of nestings available. The range of cells for the first group is B6:F16.

As a side question, will count work for a range of data or only one column or row at a time?

View Replies!   View Related
Formula Fix (today)-(month/day Of Hire) For Vacation Accrual
I'm trying to create a formula in Col D that will give me the difference of (Todays) Month/Date and (Date of Hire). The shaded rows at the end do not provide a correct answer.

How do I make the Column formula eliminate the 1 (due to hire mnth/day falling between 1/1 and current mnth/day). I've put about 30 hours in this formula, tried it many different ways. . . and realize I just can't get it.

I'm all for redoing the whole spreadsheet if you have a better way of doing vacation accruals

View Replies!   View Related
Workday Holiday Trigger Date
I am trying to work out a formula calculating dates.

basically a piece of work / report is due for completion twenty working days from a trigger date. And I need the formula to calculate this due date. Im using the workday function, which calculates a date using the number of working days given after the trigger date.

this would work fine, except that the trigger date needs to be included in the calculation (inclusive). I have tried changing the value for the number of working days to nineteen, which would logically give the right answer (20 days including the trigger day). BUT the bloody thing doesnt work, because the trigger date can be on a weekend or holiday!! so then it calculates 19 days (as it should) but the result is wrong because the trigger day isnt included (as its a holiday/weekend).

View Replies!   View Related
Filesearch Object With A Datestamp (workday-1)
I need to be able to open a filenamed in the following format.

c:/workbook20060112.txt.

However the lsat part of the string is dyanmic and works with one business days lag (hence format sheet (date, "yyyymmdd") ) is not approriate. i also have the problem that that MS Excel does not have a holiday calendar (so this would not work over bank holidays/easte/exmas ect). The folder also contains archived files as below.

c:/workbook20060112.txt
c:/workbook20061130.txt
c:/workbook20061129.txt

I have been told about a filesearch object as well as a filesystem object. What situations would you use one over the other?

What I want it the most upto data file to pick up, but I dont want to do a loop where it look through all the files or something that counts down from today until it 'hits' the correct file.

View Replies!   View Related
Workday - Adding Partial Days
When I use the formula below it works great;

= J10+(1/$D$10)

J10 = 3-1-07
D10 = 1.5 (Production Rate example .2,.5, 1.5 rate per day) The result is:

3-1-07
3-1-07
3-2-07
3-3-07
3-3-07
3-4-07
3-5-07 and so on...

however, when I try to include only workdays, it does not calculate correctly.

= WORKDAY(J10+(1/$D$10),1,Holidays!B75:B88) with the same definitions as above the result is...

3-1-07
3-2-07
3-3-07
3-4-07

View Replies!   View Related
Function That Is The Equivalent Of WORKDAY But For Hours Instead
I am in need of some excel advice relating to date calculations. Basically I need a function that is the equivalent of WORKDAY but for hours instead. I have a series of events that take a certain length of time to complete, most of them less than a day but some more than. By way of example see the screenshot below:

In reality the last three operations would have to take place on the 27th of April, with the Welding operation starting on the end of the 25th around 7pm. The plant is running a 24 hour day, and works 5 days a week. How can I calculate the times in hours offset rather than going day by day? I need to account for

* Weekends
* Fixed Holidays
* Operations running as seamlessly as possible

Any advice welcome. I have attempted to use WORKDAY with the number of days to deduct rounded to the nearest day and then subtracting the operation time but this results in errors where operations would cumulatively go over a working day. The objective is by knowing when the end product is needed and knowing how long each operation takes it is possible to discover when to start manufacture. VBA or Formula code is fine as this will be integrated into a VBA project.

View Replies!   View Related
WORKDAY Function To Calculate The Start Date
As everyone realizes that WORKDAY function can return a working date that exclude weekends and any dates identified as holidays. However, what if I want to return a working date excluding my designated holidays but including weekends?

For example,

Holidays are 1 Jun 2009, 2 Jun 2009 and 4 Jun 2009

Start Date: ???
Finish Date: 8 Jun 2009
Duration: 5 days

The Start Date should be 30 May 2009.

Seems like I am not able to use WORKDAY function to calculate the start date.

View Replies!   View Related
=WORKDAY But Includes Weekends In The Specified Number Of Days
I'm looking for a function similar to =WORKDAY but with a slight variation.

I want the formula to return the date that is the closest non-weekend, non-holiday day that is a specific number of CALENDAR days from the start date. So the formula does not return weekend or holiday days, but does include them when counting the days away from the start date.

For example if the start date was Dec 1st, and I specified 14 days after this date, I would want Dec 15th returned (as the 14th is a weekend).

View Replies!   View Related
Calculate Workday Difference Between Dates/Times
I have a spreadsheet with Received Date/Time in C2 and Resolved Date/Tim in D2, Work Start Time in E2, Work End Time in F2. C2 and D2 are in dd/mm/yyyy hh:mm AM/PM format:04/03/2008 10:27 AM06/03/2008 1:06 PM9:00:00 AM5:00:00 PM

I need to be able to calculate the resolution time of each request in hours/mins excluding weekends and public holidays. I've tried using the NETWORKDAYS function but it doesn't seem to pick up multiple days between the Received and Resolved dates/times. I have put the Public Holidays in my spreadsheet ( attached) in AA2-AG2.

View Replies!   View Related
Month Day Day Of Week
1 2 3 4 5 6
11/1 11/2 11/3 11/4 11/5 11/6
sat sun mon tues wed thur

what i am trying to do is to be able to put a date in a cell and it would fille in the month day and day of week

=$D$1
=$D$1+E5-1
=TEXT(WEEKDAY(D6),"ddd")

cells are not correct in the formulas just copied them. i got this to work in a set up xls file when used it in the real setting i get ###.

View Replies!   View Related
Find The High Price Day Of Each Week. Then Report The Median High Day
I have an interesting problem (I hope you find it interesting too). I'm trying to find the historical "high" day of the week for a particular stock I follow. I searched for several hours but was honestly unable to make any progress. Normally I do my own formulas, so other than pasting a rare macro, that's pretty much the extent of my (lack of) experience, ie please assume I am a novice....

View Replies!   View Related
Saving To A Network Drive
We have a form that is completed by our employees and emailed to a central group email address that up to 8 employees within our team can access. When one of our 8 team members open the file sent they need to add comments to it and then save it in a central directory on one of our network drives.

Now the problem I have is that we all have the network drive mapped to a different letter.

Is there a way that I can have a macro (see below for current macro that does not work) save to a network path and not a drive letter.

View Replies!   View Related
How To Get To A Computer On Our Network Using Code
I have code below that works well most of the time. One of the cases below will not work. It insists on requiring a name and password.

(note: it is the only Windows 2000 operating systems)

I want to enter this in my case line to automatically add the username and password.

Let's say the username is: "dave" and the password is: "happy"

How can I add this to this part of the code?

Case "DJ"
strfilename = "\DAVEJONESDavesProposals" & strfilename

Sub Save_and_SaveSalesman()

Dim strPath As String, strPath2 As String, CurrPath As String

Dim WB1 As Workbook
Dim WB2 As Workbook

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved