Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work Hours
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) Whe- 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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Work Hours
Try formatting the cell for custom, using [hh]:mm 1.48 is days, and is
just under 36 hours. HTH, Bernie MS Excel MVP "Kamper" wrote in message ... 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) Whe- 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Work hours calculation | Excel Discussion (Misc queries) | |||
Formula to work out hours worked | Excel Discussion (Misc queries) | |||
Work out overtime hours for individuals | New Users to Excel | |||
How can I work out how many hours I get premium rate? | Excel Worksheet Functions | |||
calculate hours on work sheet | Excel Worksheet Functions |