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. |
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. |
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. |
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. |
All times are GMT +1. The time now is 05:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com