Workdays

  • Thread starter Thread starter Andrew
  • Start date Start date
A

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
 
=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)))
 
Thank You Bob
--
Andrew


Bob Phillips said:
=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
Click to expand...
 
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
 
Back
Top