Ref: Formula to calculate elapsed time between certain dates and t

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way that the function in the referenced post could include
weekends, i.e.
10/26/05 3:48 PM 11/1/05 9:07 AM?

Function in posting:

=SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24*
(WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2)
<6,WEEKDAY(A15,2)<6))*10/24)

This function totals the time as 5:19:00 when it actually should be 29:19:00.

Thanks in advance for any help.
 
Hi

how have you got the cell with the formula formatted?
It should be [h]:mm to allow Excel to go beyond 24 hours, rather than
cycling back to zero again.

With your dates and times and formula, I get a result of 33:19

Regards

Roger Govier
 
Hello.

Changing the time format worked!

Thank you very much.

Roger Govier said:
Hi

how have you got the cell with the formula formatted?
It should be [h]:mm to allow Excel to go beyond 24 hours, rather than
cycling back to zero again.

With your dates and times and formula, I get a result of 33:19

Regards

Roger Govier

Is there a way that the function in the referenced post could include
weekends, i.e.
10/26/05 3:48 PM 11/1/05 9:07 AM?

Function in posting:

=SUM((18/24-MOD(A1,1))*(WEEKDAY(A1,2)<6),MOD(A15,1)-8/24*
(WEEKDAY(A15,2)<6)+(NETWORKDAYS(A1,A15)-SUM(WEEKDAY(A1,2)
<6,WEEKDAY(A15,2)<6))*10/24)

This function totals the time as 5:19:00 when it actually should be 29:19:00.

Thanks in advance for any help.
Click to expand...
Click to expand...
 
Back
Top