I have a report with the date and time a helpdesk ticket was opened and responded to. I need a formula to calculate the response time on helpdesk tickets between the hours of 5am-5pm. So say a user sends in a ticket at 430pm and does not receive a response till 7am the following day, the formula would return with 2.5 hours. Please help!

Let me start by saying that I hate time calculations, so there may be a much better way to do this. I hope someone else is following this thread and has a better idea. This seems to work for your example, but would need extensive testing for other values. In fact, it is not designed to return the response time for anything other than a call time before 5 PM and a response time after 5AM. It also assumes the response time happens the next day, not 2 days later. It doesn't deal with "dates" at all.

With your call time in A1 and your response time in B1, try this in C1 to get 2:30=(TIMEVALUE("17:00")-A1)+(B1-TIMEVALUE("5:00"))

The in D1 put =(C1-INT(C1))*24 to get 2.5

To combine the 2, you'd need this:

=((TIMEVALUE("17:00")-A1)+(B1-TIMEVALUE("5:00"))-INT((TIMEVALUE("17:00")-A1)+(B1-TIMEVALUE("5:00"))))*24

Try this, it might be a bit easier: Cell A1 & B1 – Format as Custom – MM/DD/YYYY H:MM

If the edit is not present then simply create it.

Cell C1 – Format as Custom – [H]:mm

If the edit is not present then create it

Enter your beginning date in Cell A1 as:

Date & Time (either AM/PM or Twenty Four hour time)

The edit will convert it to 24-hour time.Be sure to separate the date from the time and the AM/PM

03/17/2009 4:30 PM

Enter you ending date in Cell B1 same as above.

03/18/2009 7:00 AM

Now in cell C1 simply enter the formula: =B1-A1

It should show 14:30

MIKE

The OP wants the result to be 2.5. In other words, the call came in .5 hours before the end of the business day and was responded to 2 hours after the start of the next business day.

That makes the response time 2.5 business hours.

Aha, now I see..... MIKE

I really appreciate the help!

OK, Revised formulas, This one is similar but I use Hours & Minutes

so instead of 2.5 for 2 and one half hours

I use 2:30 for 2 hrs and 30 minutes.Cell A1 & B1 – Format as Time either AM/PM or 24 hrs.

Cell C1 – Format as 24hr Time (don’t use Seconds)

Enter Start time in A1

Enter End time in B1In Cell C1 enter the formula:

=IF(A1>B1,((B1+.5)-A1),B1-A1)

In my limited testing it worked.

MIKE

I didn't test too many start/end times, but if you wrap Mike's suggestion in the "convert H:M to decimal time" syntax, you can get 2.5, etc. =(IF(A1>B1,((B1+0.5)-A1),B1-A1)-INT(IF(A1>B1,((B1+0.5)-A1),B1-A1)))*24

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History