Workdays

[Andrew]

I am using the following formula to calculate workday
=SUMPRODUCT(INT((MAX(K9,J9)-WEEKDAY(MAX(K9,J9)+1-{2;3;4;5;6})-MIN(K9,J9)+8)/7))

However I am experiencing the following problems:

When no dates are captured in start or end date it calculates an #NUM! error

I have more than one interval where the end date in the one formula becomes
the start date of the next formula. If i capture the end date in the one
calculation but no end date in the next calculation a 1 day total is
calculated while it should be 0 until a end date is completed

 

[Bob Phillips]

=IF(OR(J9="",K9=""),0,SUMPRODUCT(INT((MAX(K9,J9)-WEEKDAY(MAX(K9,J9)+1-{2;3;4;5;6})-MIN(K9,J9)+8)/7)))

 

[Andrew]

Thank You Bob
--
Andrew

=IF(OR(J9="",K9=""),0,SUMPRODUCT(INT((MAX(K9,J9)-WEEKDAY(MAX(K9,J9)+1-{2;3;4;5;6})-MIN(K9,J9)+8)/7)))

--
__________________________________
HTH

Bob

 

[Shane Devenshire]

Hi Andrew,

Why not use the NETWORKDAYS function which calculates the number of workdays
between two dates excluding weekends and if you so choose holidays?

This function is part of the Analysis ToolPak - attach it by choosing Tools,
Add-Ins, Analysis ToolPak.

Cheers,
Shane Devenshire
Microsoft Excel MVP