Help with Excel formula

Dell / 3100
March 17, 2009 at 10:39:36
Specs: Windows XP Pro, 2.8GHz/2GB
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!

See More: Help with Excel formula

Report •

March 17, 2009 at 11:46:52
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


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

To combine the 2, you'd need this:


Report •

March 17, 2009 at 12:57:13
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


Report •

March 17, 2009 at 13:11:44
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.

Report •

Related Solutions

March 17, 2009 at 13:16:39
Aha, now I see.....


Report •

March 17, 2009 at 14:46:37
I really appreciate the help!

Report •

March 17, 2009 at 17:06:26
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 B1

In Cell C1 enter the formula:


In my limited testing it worked.


Report •

March 17, 2009 at 17:22:18
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.


Report •

Ask Question