Date Diff Calculation

[jennjcooper]

Hi All,

I'm desperately seeking an answer to my problem.

Cell
A1= 12/10/07
D1 = 15/10/07
E1=D1-A1
F1= 16/10/07
G1= F1-A1
H1= 17/10/07
I1=H1-A1

(The above dates are in the british format of dd/mm/yy btw)
What I would like to do is have E1, G1 and I1 returning how many days transpired between the two dates independently of each other. That's simple enough to do I know but I need it to only show WORKING days and not include Saturdays and Sundays in the final figure.

If you tap those figures into Excel you get this;

E1= 3
G1=4
H1=5

However, there is a weekend between 12/10 and 15/10/07, so I want them to say

E1=1
G1=3
H1=4

Any ideas on the formula for this? Or would it be better to whack a bit of code in? If so, could you tell me how to go about doing that please? I know it's tricky so I'm sorry.

Thanks again!!

Jenn

[Richard Schollar]

Hello Jenn

If you have the Analysis Toolpak installed (check in Tools>Add-Ins) then you can use the NETWORKDAYS function:

=NETWORKDAYS(Date1,Date2)

you have the potential to include holiday days to exclude (eg Public Holidays) as an optional 3rd argument to the function. This function excludes Saturdays and Sundays.

Richard

[oldchippy]

Take a look at this link, similar question

http://www.excelforum.com/showthread.php?p=1821092

[NBVC]

If you have the Analysis Toolpak installed (Tools|Addins) then you could use the Networkdays() function, which will give you the number of days between dates excluding weekends (and you can also avoid holidays too...)

eg.

=Networkdays(A1,D1)

to use the holidays feature, you need to have a list of holidays somewhere and then the formula would be:

=Networkdays(A1,D1,holidays).