Work Hours

[Kamper]

I tried this, but had to change the cell references, but that did not work.
Below is my example. Do you see what I'm doing wrong?

Here is my formula:
=(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D2,C2)

A2 = 1/16/2008 10:00 am
B2 = 1/22/2008 9:34 am
C2 = 08:00 am
D2 = 17:00 pm

My results are showing 1.48

Thanks,
Kamp

Hi,

Try this

=(NETWORKDAYS(A1,A2)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

Where:-
A1= Earlier date/time
A2= Later date/time
B1 = 08:00
B2 = 17:00

Mike

I need to calculate the total WORK-hours (08:00-17:00) between two
date/time-stamps;
-excluding WEEKENDS
-excluding PUBLIC HOLIDAYS

eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 11-01-2008 09:00:00
A2 11-01-2008 11:00:00
A3 02:00

eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 11-01-2008 09:00:00
A2 14-01-2008 11:00:00
A3 11:00

eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 14-01-2008 09:00:00
A2 16-01-2008 11:00:00
A3 20:00

 

[Bernie Deitrick]

Try formatting the cell for custom, using [hh]:mm 1.48 is days, and is
just under 36 hours.

HTH,
Bernie
MS Excel MVP

I tried this, but had to change the cell references, but that did not work.
Below is my example. Do you see what I'm doing wrong?

Here is my formula:
=(NETWORKDAYS(A2,B2)-1)*(D2-C2)+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),D2,C2),D2)-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),D2,C2)

A2 = 1/16/2008 10:00 am
B2 = 1/22/2008 9:34 am
C2 = 08:00 am
D2 = 17:00 pm

My results are showing 1.48

Thanks,
Kamp

Hi,

Try this

=(NETWORKDAYS(A1,A2)-1)*(B2-B1)+IF(NETWORKDAYS(A2,A2),MEDIAN(MOD(A2,1),B2,B1),B2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),B2,B1)

Where:-
A1= Earlier date/time
A2= Later date/time
B1 = 08:00
B2 = 17:00

Mike

I need to calculate the total WORK-hours (08:00-17:00) between two
date/time-stamps;
-excluding WEEKENDS
-excluding PUBLIC HOLIDAYS

eg#1.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 11-01-2008 09:00:00
A2 11-01-2008 11:00:00
A3 02:00

eg#2.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 11-01-2008 09:00:00
A2 14-01-2008 11:00:00
A3 11:00

eg#3.: A1=START DATE/TIME & A2=END DATE/TIME & A3=RESULT ([hh]:mm)
A1 14-01-2008 09:00:00
A2 16-01-2008 11:00:00
A3 20:00