using TIME in an IF(AND function

[Newsome79]

Hi All,

I'.m stuck and wondering if anyone can help. Essentially i have the time a job was requested. Depending on when in the day that was dictates how long we have to complete it. Basically if we receive it between 20:00 and 08:00 the following day its a 4 hour SLA, if we receive it on a Saturday or Sunday its a 4 hour SLA, all other times its a 3 hour SLA.

I then need to work out how long it took us to do the job and whether that falls within the right time bands.

So i have my date of request which i then change the format to give me the actual Day of the Week, then the time the job was received. This is formatted as TIME and shows as HH:MM:SS

05/06/2016 SUNDAY 20:25:00

From this i've tried to use the following formula (ECO being the job type code). I've included blank spaces after Sunday as it only seems to work when there are 9 characters in this field. I'm assuming that's because Wednesday is 9 characters long, so all other days get padded with space?

=IF(AND(AN65="ECO",M65="SUNDAY "),"4 Hours SLA",IF(AND(AN65="ECO",M65="SATURDAY "),"4 Hours SLA",IF(AND(P65>=TIME(20,0,0),P65<=TIME(8,0,0)),"4 Hour SLA","3 Hour SLA"))))

So i've tried each component part on its own, IF(AND(AN65="ECO",M65="SUNDAY "),"4 Hours SLA", gives me the answer i need as does IF(AND(AN65="ECO",M65="SATURDAY "),"4 Hours SLA", at first i just had everything else showing as "3 Hour SLA but that's not enough i need to build the time factor in there as well. I've tried showing the time as 20:00:00 and 20,0,0 as above but it still doesn't work. in the example above when i remove Sunday from the spreadsheet to test it it tells me that 20:25:00 would be a 3 hour SLA which is wrong. What am i doing wrong?

This is also causing me issues on the next stage of the spreadsheet as having worked out how long it took us to reach the job i then need to see whether we hit SLA for which i've used =IF(AND(AT65="4 Hours SLA",AU65>="04:00:00"),"SLA Fail",IF(AND(AT65="3 Hours SLA",AU65>="03:00:00"),"SLA FAIL",""))) Again this isn't working but i don't know if that's because the first stage is wrong so will not produce output on the second formula or if i also have issues here.


Any help very much appreciated!!!

[Pete_UK]

The time in P65 can't be BOTH >20:00:00 AND <8:00:00 at the same time - change the AND to OR. Also, you can simplify the first part like this:

=IF(AND(AN65="ECO",WEEKDAY(D65,2)>5),"4 Hours SLA",IF(OR(P65>=TIME(20,0,0),P65<=TIME(8,0,0)),"4 Hour SLA","3 Hour SLA")))

I've assumed that column D contains the date - change the item in red to suit.

Hope this helps.

Pete

[Newsome79]

Thanks Pete, loving the simplified bit, makes it look much tidier but i still don't think its working. The bit about WEEKDAY(D65,2)>5), what does this do? I'm assuming from something like 05/06/16 it looks at the 05 bit but i don't understand what the >5 does?

[Pete_UK]

The WEEKDAY function works on a date and returns the day of the week as a number. With the second parameter set to 2, it returns 1 for Monday, 2 for Tuesday etc. so weekends are either 6 or 7 (i.e. greater than 5).

Hope this helps.

Pete