Help with date ranges

M

MattG

Hi all,

I've got a sheet where I have some columns with dates in and I want a
column with the number of working days between them.

I've used the DAYS360 function, which gives a rough indication of the
number of days, but it's not very accurate. Can anyone give me an idea
of now to do it, a script, a plug in, or a different app I can download
to do it?

Help really appreciated.
 
R

Ron Coderre

Try one of these:

If you have the Analysis ToolPak add-in installed and enabled:
Use the NETWORKDAYS function

OR
If you want to use standard Excel functions, here's one way:
A1: (startdate)
B1: (enddate)
C1: =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6))
Or...to avoid volatile functions:
C1: =SUMPRODUCT(--(WEEKDAY(ROW(INDEX(A:A,A1):INDEX(A:A,B1)),2)<6))

Note: the NETWORKDAYS function can easily accommodate holidays while the
SUMPRODUCT version will need an additional section.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro
 
M

MattG

Peo said:
Look at NETWORKDAYS and WORKDAY in help, they are part of
office/excel but need to be installed (Analysis ToolPak)
Click to expand...

Thanks Peo and Ron, much appreciated. Does exactly what I needed to do.
 
Top