Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Hi!
Does anyone know how to calculate working hours between 2 dates ? The 2 examples I found on the Internet does not work For example this one: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) StartDT: 8.1.2008 05:00 EndDT: 8.1.2008 08:00 DayStart: 8:00 DayEnd: 16:00 gives as a result 3 hours (it should of course be 0) Does anyone know how to fix it so that it calculates correctly ? There is another solution for calculating the working hours between 2 dates but it has the same problem Thanks, Jani |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Jani,
Maybe this:- =((NETWORKDAYS(A1,B1)-1)*(D$2-D$1)+IF(NETWORKDAYS(B1,B1),MEDIAN(MOD(B1,1),D$2,D$ 1),D$2)-MEDIAN(NETWORKDAYS(A1,A1)*MOD(A1,1),D$2,D$1))*24 Where A1 = Start date & Time B1 = End Date/Time D1 =day start time D2 =Day end time The formula can be dragged down for different start/end periods in columns A & B. Mike "Jani Ruohomaa" wrote: Hi! Does anyone know how to calculate working hours between 2 dates ? The 2 examples I found on the Internet does not work For example this one: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) StartDT: 8.1.2008 05:00 EndDT: 8.1.2008 08:00 DayStart: 8:00 DayEnd: 16:00 gives as a result 3 hours (it should of course be 0) Does anyone know how to fix it so that it calculates correctly ? There is another solution for calculating the working hours between 2 dates but it has the same problem Thanks, Jani |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Jani,
I should add, format the result as a number with zero decimal places. Mike "Jani Ruohomaa" wrote: Hi! Does anyone know how to calculate working hours between 2 dates ? The 2 examples I found on the Internet does not work For example this one: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) StartDT: 8.1.2008 05:00 EndDT: 8.1.2008 08:00 DayStart: 8:00 DayEnd: 16:00 gives as a result 3 hours (it should of course be 0) Does anyone know how to fix it so that it calculates correctly ? There is another solution for calculating the working hours between 2 dates but it has the same problem Thanks, Jani |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Sat, 26 Jan 2008 11:45:20 +0200 from Jani Ruohomaa
: Does anyone know how to calculate working hours between 2 dates ? Look in Help for NETWORKDAYS, and multiply by the number of working hours per workday. You will need Analysis Toolpak if it's not already installed. -- Stan Brown, Oak Road Systems, Tompkins County, New York, USA http://OakRoadSystems.com/ "If there's one thing I know, it's men. I ought to: it's been my life work." -- Marie Dressler, in /Dinner at Eight/ |
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Mike H kirjoitti:
Jani, I should add, format the result as a number with zero decimal places. Mike "Jani Ruohomaa" wrote: Hi! Does anyone know how to calculate working hours between 2 dates ? The 2 examples I found on the Internet does not work For example this one: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) StartDT: 8.1.2008 05:00 EndDT: 8.1.2008 08:00 DayStart: 8:00 DayEnd: 16:00 gives as a result 3 hours (it should of course be 0) Does anyone know how to fix it so that it calculates correctly ? There is another solution for calculating the working hours between 2 dates but it has the same problem Thanks, Jani Thanks a million, it seems to work! |
#6
Posted to microsoft.public.excel.worksheet.functions, microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Hello Jani,
Do Saturdays and Sundays count, too? If you want to count indicidual hours per weekday: http://www.sulprobil.com/html/count_hours.html Regards, Bernd |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Hi!
It seems that it gives as a result for these to dates 16 hours but it should be 0 since 12th and 13th are saturday and sunday and non-working days start date 12.1.2008 08:00 end date 13.1.2008 08:00 If I put start date as 11.1.2008 08:00 and end date as 12.1.2008 08:00 or 13.1.2008 08:00 it is correct, 8 hours 11th is a friday and a working day Jani Mike H kirjoitti: Jani, I should add, format the result as a number with zero decimal places. Mike "Jani Ruohomaa" wrote: Hi! Does anyone know how to calculate working hours between 2 dates ? The 2 examples I found on the Internet does not work For example this one: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) StartDT: 8.1.2008 05:00 EndDT: 8.1.2008 08:00 DayStart: 8:00 DayEnd: 16:00 gives as a result 3 hours (it should of course be 0) Does anyone know how to fix it so that it calculates correctly ? There is another solution for calculating the working hours between 2 dates but it has the same problem Thanks, Jani |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
calculating working hours between 2 dates
Hi,
On my computer it gives a result of 0 for those dates/times which is correct because as you point out they are a saturday and sunday If I extend the dates to 12/01/2008 08:00 14/01/2008 08:00 This also returns (correctly) 0 and 12/01/2008 08:00 14/01/2008 16:00 Returns 8 I can't explain the erronoeous results you are getting, the formula is correct. Mike "Jani Ruohomaa" wrote: Hi! It seems that it gives as a result for these to dates 16 hours but it should be 0 since 12th and 13th are saturday and sunday and non-working days start date 12.1.2008 08:00 end date 13.1.2008 08:00 If I put start date as 11.1.2008 08:00 and end date as 12.1.2008 08:00 or 13.1.2008 08:00 it is correct, 8 hours 11th is a friday and a working day Jani Mike H kirjoitti: Jani, I should add, format the result as a number with zero decimal places. Mike "Jani Ruohomaa" wrote: Hi! Does anyone know how to calculate working hours between 2 dates ? The 2 examples I found on the Internet does not work For example this one: =IF(AND(INT(StartDT)=INT(EndDT),NOT(ISNA(MATCH(INT (StartDT),HolidayList,0)))) ,0,ABS(IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2), (24*(DayEnd-DayStart)* (MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+ INT(24*(((EndDT-INT(EndDT))- (StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-DayStart))))+ MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+ (24*DayEnd-(24*(StartDT-INT(StartDT)))),2), ROUND((24*(DayEnd-DayStart)),2)))))) StartDT: 8.1.2008 05:00 EndDT: 8.1.2008 08:00 DayStart: 8:00 DayEnd: 16:00 gives as a result 3 hours (it should of course be 0) Does anyone know how to fix it so that it calculates correctly ? There is another solution for calculating the working hours between 2 dates but it has the same problem Thanks, Jani |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculating working hours between 2 dates | Excel Worksheet Functions | |||
Calculating time increments from dates during working hours | Excel Worksheet Functions | |||
Calculating working hours | Excel Worksheet Functions | |||
Calculating working hours | Excel Discussion (Misc queries) | |||
Calculating working hours | Excel Discussion (Misc queries) |