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

This do you? I've had to save it as a tab delimited *.txt file, but you can always import it into Excel and do what you need to do!

The only trouble is, the callout times and response times have to be entered as decimal fractions of hours (OK for the response time as that's set at 2 hrs, 25 hrs, or 168 hrs (7 days) but a callout time of 9:15 am would need to be entered as 9.25, and 1:05 pm as 13.08) As a result, I've had to add two fields (E2 and F2 in the spreadsheet that opens when you import the *.txt file), which convert the times entered into minutes which allows the formula to work. These fields could be put anywhere, of course, and I'm sure a bit more work would solve these issues!

The only other thing is the date in the date output field (B2 in the *.txt file spreadsheet) might need to be tinkered with, 'cos while the formula recognises UK-format dates, it'll only display US-format dates. Hence, I've had to make it spell out the date...

HTH

Can you precede the daytime formula by another "If"? I.e. If calldate + time is > (greater than) (call date +1 - 2hrs), then calldate+1, otherwise ... [your formula for pre 10 pm here]. You can nestle lots of Ifs of course.

Incidentally, on Lotus 123 if was possible to fix the date as an absolute. This might or might not be relevant:

When calculating interest on a daily basis, the amount on a given day would increase as the clock moved forward through the 24 hr period. Therefore, if you are adding dates and times together, the clock might be rolling forward, so screwing up the answer. Not sure how to fix dates at midnight in Excel off the top of my head - sorry.

Any good?

Mo

Its to do with the way excel handles times I think. Your original formula will work if you change J6>"21:59", to hour(j6)>21

Thanks for all those suggestions - will work through them this afternoon and see what I get sorted.

Cheers!