Workdays
 

I am using the following formula to calculate workdays
=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
--
Andrew

Workdays
 

=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

"Andrew" wrote in message
...

I am using the following formula to calculate workdays
=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
--
Andrew

Workdays
 

Thank You Bob
--
Andrew


"Bob Phillips" wrote:

=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

"Andrew" wrote in message
...

I am using the following formula to calculate workdays
=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
--
Andrew

Workdays
 

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

"Andrew" wrote in message
...

I am using the following formula to calculate workdays
=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
--
Andrew