Work Hours

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kamper - 30 Jan 2008 22:43 GMT

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

"Mike H" wrote:

> 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
>
> "CHRISTI" wrote:
>
> > 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

Reply to this Message

Bernie Deitrick - 31 Jan 2008 03:35 GMT

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?
[quoted text clipped - 49 lines]
>> > A2 16-01-2008 11:00:00
>> > A3 20:00

Reply to this Message