Excel : Calculating Elapsed Time

[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

 

[Norman Harker]

Hi Joy!

Try:
=NETWORKDAYS(A1,B1)+MOD(B1,1)-MOD(A1,1)-1

 

[Frank Kabel]

Hi Norman
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

 

[Norman Harker]

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!)

 

[Daniel.M]

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

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.

 

[Norman Harker]

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)