Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2005

    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??


  2. #2
    Join Date
    Apr 2004
    Derbyshire, UK
    Provided Answers: 2
    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 !



Posting Permissions

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