Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Time Lapse Calculation

8 views
Skip to first unread message

Big Tony

unread,
Oct 10, 2006, 12:50:02 PM10/10/06
to
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

unread,
Oct 10, 2006, 12:57:56 PM10/10/06
to
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

unread,
Oct 10, 2006, 2:15:02 PM10/10/06
to
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

unread,
Oct 10, 2006, 6:10:03 PM10/10/06
to
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

unread,
Oct 11, 2006, 10:41:02 AM10/11/06
to
This formula works great. Thanks!
0 new messages