Thread: Excel date calculation...

1. Registered User
Join Date
Dec 2005
Posts
19

Unanswered: Excel date calculation...

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.

Any ideas??

Cheers

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Hi mrbump

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.

=IF(G6="2 Hrs",(I6+J6)+2/24,"???")

or pehaps =(I6+J6)+2/24 as this will alway be correct for 2 hr responce !

HTH

MTB

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•