I have a spreadsheet which monitors response time to emails. The spreadsheet looks at the time an email was received, and the time it was replied to, and if the difference is more than 3 hours, it highlights this.
I achieve this by using, in K1 the following; =IF(NOT(ISBLANK(A1))*AND(J1<=0),"YES","NO")
Where J1 is the result of the turnaround cell (time taken to reply) minus the SLA cell (3 hours). So if an email was received at 13:00:00 and replied to at 14:00:00 the turnaround cell would show 1:00:00 and J1 would output -2:00:00, resulting in K1 showing YES. This works fine and properly highlights with NO emails that are outside of the SLA. I also use a conditional formatting to change the cell colour in this case. (I also use 1904 date system to display the negative time that results)
I now need to extend this function where the response time might be over 24 hours, and the formula breaks. I understand this is because excel has some issues with adding up of times but I was hoping there could be some trickery to get around this. Basically I want to be able to do as above but take into account an email received on Monday at 16:00 not being replied to until Tuesday at 17:00, a 25 hour difference.
I have all of my time cells using a custom format of [h]:mm:ss so excel will count over 24 hours, but I'm just stuck at how to have excel output that 25 hour difference, any help on a formula that has excel output the sum of two times that exceed 24 hours would be greatly appreciated!
(I have attached an example sheet to show my current method)
Bookmarks