[t long]

I need a formula that will take the Start Date in cell A1 and add the number in cell B1 to it exluding weekends (sometimes It is either 1 day too much or one day too less - using the WORKDAY(A1,B1) function. If the start date is 12 Feb 2007 and I want to add 5 days, I need it to include the 12th of Feb as the first day unless its a wknd of course. Also I need to do a total count once I have received an end date (by adding A1 to B1) a total count of wknds between the two. (keeping in mind if there is a wknd before the start date and after they will be included)

So for example:

Start Date = 12 Feb 2007 and I want to add 6 working days (cell B1)

End Date = 19 Feb 2007

Weekends = 4 (10th, 11th, 17th & 18th)

Can you help me figure this one out?

 

[etaf]

i used a macro to work out this years ago for calculating the elapsed time on a contract that did not include weekends and Mon- Fri 9:00 - 17:00

I think i still have it and will look up and post here

 

[etaf]

OK - so workday works -but you want to count the workday as a 1

you could use an if statement using weekday

so if the cell contains a weekday {use weekday(cell, 2) so anything greater than 5 is a weekend)

so in this case we have

cell A1 12th feb
cell B1 = 6

but you only want to move 5 working days as 12th is a workday

if((weekday(a1,2)>5), workday(a1,b1),workday(a1,(b1-1)))

this will return the same day if its a start date on a saturday, sunday, monday

ie start date of
17-Feb sat
18-Feb sun
19-Feb mon

Add 6 working days - and if a workday include that day

result =
26-Feb
26-Feb
26-Feb

all a monday = 6 working days later - again the 19th = mon and include that as the day

 

[The Villan]

If you were able to put just a date in B1 you could use

NETWORKDAYS

Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click OK.
If necessary, follow the instructions in the setup program.
Syntax

NETWORKDAYS(start_date,end_date,holidays)

Important Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Start_date is a date that represents the start date.

End_date is a date that represents the end date.

Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant of the serial numbers that represent the dates.

 

[etaf]

added the Networkdays formula onto my calc to see if it returned 6 for all cases

it worked

updated the example spreadsheet

 

[The Villan]

=NETWORKDAYS(A2,IF((WEEKDAY(A2,2)>5), WORKDAY(A2,B2),WORKDAY(A2,(B2-1))))

with the cell formatted to general