Excel : Calculating Elapsed Time

  • Thread starter Thread starter Joy
  • Start date Start date
J

Joy

I am trying to calculate elapsed time between a start date
and time and an end date and time. However, I do not want
the formula to calculate week-ends i.e. only weekdays are
to be taken into account. I have managed the formula
without the exclusion of week-ends.

thanks

Joy
 
Hi Frank!

Aaaaaagh!

My inclination in the context of the question is to data validate the
inputs to prevent entries of Saturday and Sunday (or any Holiday!)
 
Hi Frank,
but this will give you probably wrong answers, if either A1 or B1 are a
Saturday/Sunday :-)

For the OP: I think Chip has a (more complicated) version on his site:
http://www.cpearson.com/excel/DateTimeWS.htm
Click to expand...


Chip's formulas (the two first on the page you indicated) are related to cases
where you want to count hours included in normal working hours (9:00 to 17:00 as
he indicated). Even so, it doesn't work either for cases where you are ending
your shift on the weekend.

The following should be OK.
=NETWORKDAYS(A1,B1)
-IF(NETWORKDAYS(B1,B1),1-MOD(B1,1))
-IF(NETWORKDAYS(A1,A1),MOD(A1,1))

Format as [h]:mm

You can add an Holidays range as a 3rd parameter to Networkdays() if you see
fit.

Regards,

Daniel M.
 
Hi Frank!

Another poster has posted the same question only this time they want
to allow starting and stopping on a weekend day.

I get the following by way of building on a case basis but no doubt
the approach can be improved upon.

=NETWORKDAYS(A1,B1)+MOD(B1,1)-MOD(A1,1)-1+IF(WEEKDAY(A1,3)>4,MOD(A1,1),0)+IF(WEEKDAY(B1,3)>4,1-MOD(B1,1),0)
 
Back
Top