I'm goign about setting up a spreadsheet in excel to monitor staff responses to callouts, comparing our anticipated and the actual response dates/times.
I'm basing the anticipated response time on the call priority (2 hrs, 25 hrs or 7days) and the call date, e.g. if the priority is 7 days then the we expect someone to respond on or before the call date plus 7 days.
However I'm having trouble with the 2 hour response. The call date and time are held in 2 seperate fields, and initially i thought for 2 hr response leave the date field as it is (i.e. same day) and just add 2 hours to the response time. That works fine until the call is raised after 10pm, where the date needs to be rolled forward to the next day but i can't find a method to calculate this.
Basic principle is "IF priority = 2 hrs and call raised time > 22:00 then date = call raised date + 1" and I've tried various formulae, including =IF((G6="2 hrs")*AND(J6>"21:59"),I6+1,I6) which I thought would nail it but it didn;t come close
Calculating the expected response time field is easy - if it's a 2 hr response add 2 hours to the call time, otherwise it stays the same.
Assuming G6 contains the literal "2 Hrs" as the priority (and I6 is a real date and J6 is a read time not a literal "22:59" as in your example) then this should work provided the cell is formatted as a "mm/dd/yy", although "dd/dd/yy hh:mm" would also work if you are interested in the time also.
or pehaps =(I6+J6)+2/24 as this will alway be correct for 2 hr responce !