Time Lapse Calculation

[Big Tony]

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

 

[vezerid]

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

 

[Big Tony]

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.

 

[daddylonglegs]

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]

This formula works great. Thanks!

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

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