Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Lapse Calculation
Hi All,
I am trying to calculate the amount of time it takes to process help desk tickets. I have used the NETWORKDAYS formula but it only returns whole days. Therefore, if a ticket is logged at 5:25 PM on Monday and completed at 8:00 AM Tuesday the NETWORKDAYS is 2. I would like to be able to input date and time started and date and time ended and have the calculation return days, hours, minutes. Of course I need it to skip weekends and holidays like the NETWORKDAYS formula. Any help woudl be appreciated. A bonus calculation woultd be to exclude non work hours. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Lapse Calculation
Tony,
Therefore, if a ticket is logged at 5:25 PM on Monday and completed at 8:00 AM Tuesday the NETWORKDAYS is 2. What would you like in this case? 0 days and 14:35 hours? Also, is there a chance that date/time started/ended are holidays? Or will they always be workdays? Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Lapse Calculation
Actually I would like something where I can get and average "numer of days".
The data/time started/ended are always workdays, no weekends or holidays. "vezerid" wrote: Tony, Therefore, if a ticket is logged at 5:25 PM on Monday and completed at 8:00 AM Tuesday the NETWORKDAYS is 2. What would you like in this case? 0 days and 14:35 hours? Also, is there a chance that date/time started/ended are holidays? Or will they always be workdays? Kostis Vezerides |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Lapse Calculation
If time/date logged is in A2 and time/date completed in B2 then you can use
this formula to return the total working hours between the two =(NETWORKDAYS(A2,B2)-1)*("19:00"-"07:00")+MOD(B2,1)-MOD(A2,1) format as [h]:mm this assumes that your working day (Monday to Friday) starts at 07:00 and ends at 19:00, adjust as necessary "Big Tony" wrote: Hi All, I am trying to calculate the amount of time it takes to process help desk tickets. I have used the NETWORKDAYS formula but it only returns whole days. Therefore, if a ticket is logged at 5:25 PM on Monday and completed at 8:00 AM Tuesday the NETWORKDAYS is 2. I would like to be able to input date and time started and date and time ended and have the calculation return days, hours, minutes. Of course I need it to skip weekends and holidays like the NETWORKDAYS formula. Any help woudl be appreciated. A bonus calculation woultd be to exclude non work hours. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time Lapse Calculation
This formula works great. Thanks!
"daddylonglegs" wrote: If time/date logged is in A2 and time/date completed in B2 then you can use this formula to return the total working hours between the two =(NETWORKDAYS(A2,B2)-1)*("19:00"-"07:00")+MOD(B2,1)-MOD(A2,1) format as [h]:mm this assumes that your working day (Monday to Friday) starts at 07:00 and ends at 19:00, adjust as necessary "Big Tony" wrote: Hi All, I am trying to calculate the amount of time it takes to process help desk tickets. I have used the NETWORKDAYS formula but it only returns whole days. Therefore, if a ticket is logged at 5:25 PM on Monday and completed at 8:00 AM Tuesday the NETWORKDAYS is 2. I would like to be able to input date and time started and date and time ended and have the calculation return days, hours, minutes. Of course I need it to skip weekends and holidays like the NETWORKDAYS formula. Any help woudl be appreciated. A bonus calculation woultd be to exclude non work hours. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time Sheet Calculation | Excel Discussion (Misc queries) | |||
#VALUE! in Time Calculation | Excel Worksheet Functions | |||
perform calculation only if current time is after 10 am | Excel Worksheet Functions | |||
"Manual calculation" takes less time. Why? | Excel Discussion (Misc queries) | |||
time calculation excel | Excel Worksheet Functions |