# 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

#1
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=(TIMEVALUE("17:00")-A1)+(B1-TIMEVALUE("5:00"))The in D1 put =(C1-INT(C1))*24 to get 2.5To 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

Report •

#2
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:MMIf the edit is not present then simply create it.Cell C1 – Format as Custom – [H]:mmIf the edit is not present then create itEnter 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/PM03/17/2009 4:30 PMEnter you ending date in Cell B1 same as above.03/18/2009 7:00 AMNow in cell C1 simply enter the formula: =B1-A1It should show 14:30MIKE

Report •

#3
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

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

Report •

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

Report •

#6
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 hoursI 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

Report •

#7
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.=(IF(A1>B1,((B1+0.5)-A1),B1-A1)-INT(IF(A1>B1,((B1+0.5)-A1),B1-A1)))*24

Report •