Calculated Help Desk Ticket

  • Thread starter Thread starter Barry Campbell
  • Start date Start date
B

Barry Campbell

Hi,

I am trying create a formula that will give me the time it took to close a
help desk ticket. The formula must take in consideration holidays, weekends,
and the 8 hour workday. I am aware of the Networkdays function, but that
only gives me the number of days.

Sample Data

Open Date/Time 8/10/2004 13:05
Closed Date/Time 8/12/004 9:52

Please help...
 
Thanks for the link. The problem is that the formula cannot handle trouble
tickets older than one day. I am looking for a forumla that can handle any
range of dates.
 
The formula can handle any range of dates. Using your start and end
dates, with working hours 9:00 to 17:00, the hours worked totals 12.78

What result did you get, and what result were you expecting?
 
I am analyzing help desk tickets. In most cases where a part was required,
the ticket was open for more than one days (upto a month). I would like to
calculate the total number of minutes it took to close a ticket.

Example:
Open ticket 12/3/04 10:00
Close Ticket 12/8/04 13:03
Work Start Time 8:00
Work End Time 17:00

The above ticket would take in consideration weekends and holidays. The
total number of minutes to close the ticket was 1,803.

Friday: 420
Saturday: 0
Sunday: 0
Monday: 540
Tuesday: 540
Wednesday: 303

Thank you in advance for your assistance....
 
If you add: *60
at the end of Chip's formula, you'll get 1803 minutes, using your times.

I can email my sample workbook to you, if you're still having trouble.
 
Please email me your workbook.

Thanks.....


Debra Dalgleish said:
If you add: *60
at the end of Chip's formula, you'll get 1803 minutes, using your times.

I can email my sample workbook to you, if you're still having trouble.
Click to expand...
 
Chip's put the following note:
NOTE: These formulas will not work properly if the working day starts at a
later time of day than it ends. For example, if your working day crosses
midnight, the formulas will not work properly.
 
Your work day starts at 8:00 and ends at 17:00, so it doesn't cross
midnight.
 
Back
Top