Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Calculating working hours
I want to calculate the number of working hours between two dates.
As an example: I receive a fault call and log the date and time. I also record the date and time when it is resolved. I then need to calculate the number of hours it took to resolve the call taking into account the fact that I only work Monday - Friday & 9am to 5pm. I can calculate the hours between the two points easily enough but can't find a way to account for the non-working hours over night and the weekends. I am using Excel 2000 & XP. Any help/ideas gratefully received. Thanks. |
#2
|
|||
|
|||
http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours
-- Kind regards, Niek Otten Microsoft MVP - Excel "John" wrote in message ... I want to calculate the number of working hours between two dates. As an example: I receive a fault call and log the date and time. I also record the date and time when it is resolved. I then need to calculate the number of hours it took to resolve the call taking into account the fact that I only work Monday - Friday & 9am to 5pm. I can calculate the hours between the two points easily enough but can't find a way to account for the non-working hours over night and the weekends. I am using Excel 2000 & XP. Any help/ideas gratefully received. Thanks. |
#3
|
|||
|
|||
Hi Niek
I had already tried this one - can't make it work. Kind regards John "Niek Otten" wrote: http://www.cpearson.com/excel/DateTimeWS.htm#WorkHours -- Kind regards, Niek Otten Microsoft MVP - Excel "John" wrote in message ... I want to calculate the number of working hours between two dates. As an example: I receive a fault call and log the date and time. I also record the date and time when it is resolved. I then need to calculate the number of hours it took to resolve the call taking into account the fact that I only work Monday - Friday & 9am to 5pm. I can calculate the hours between the two points easily enough but can't find a way to account for the non-working hours over night and the weekends. I am using Excel 2000 & XP. Any help/ideas gratefully received. Thanks. |
#4
|
|||
|
|||
Hi!
Enter the date/time in a single cell: A1 = 10/1/2004 2:25 PM A2 = 10/15/2004 5:02 PM =IF(NETWORKDAYS(A1,A1)=1,17/24-MOD(A1,1),0) +IF(NETWORKDAYS(A2,A2)=1,MOD(A2,1)-9/24,0)+NETWORKDAYS(A1+1,A2-1)*8/24 Format the cell as [h]:mm Also, if you want to account for holidays, that is, exclude holidays because those days are not worked, then you need to make a list of those holidays and include a reference to that list as a 3rd argument to the Networkdays function. Biff "John" wrote in message ... I want to calculate the number of working hours between two dates. As an example: I receive a fault call and log the date and time. I also record the date and time when it is resolved. I then need to calculate the number of hours it took to resolve the call taking into account the fact that I only work Monday - Friday & 9am to 5pm. I can calculate the hours between the two points easily enough but can't find a way to account for the non-working hours over night and the weekends. I am using Excel 2000 & XP. Any help/ideas gratefully received. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating working hours | Excel Discussion (Misc queries) | |||
calculating hours | Excel Worksheet Functions | |||
Calculating working hours | Excel Discussion (Misc queries) | |||
need help w/formula for calculating overtime hours | Excel Worksheet Functions | |||
Help! I am stuck calculating Days, Hours, Mins please help | Excel Worksheet Functions |