Formula Help

[ijheyburn]

Afternoon

I hope some kind some may be able to help me.

I have a worksheet where I would like to be able to calculate an anecdotal 'Result' against some date based fields. I.e. 'Response SLA' and 'Actual Response' - I would like to be able to compare these 2 dates and if the Actual SLA exceeds the response SLA I would like the 'Result' to calculate as 'Failed'. Similarly if the Actual does not exceed the Response I would like the Result to be 'Passed'. This is complicated by the fact that there are on occasion legitimate instances where there is no Response SLA or where there is no Actual SLA, albeit if the current date/time is beyond the response SLA I would still like the result to be failed. Where this is no data response SLA field I would like the 'Result' to read 'No Response Measure' or such like.

I am managing to do this at the moment with a couple of helper columns and a final calculation in a 3rd helper column but wonder if there is a way to do it in 1 succinct formula? I hope I've explained this correctly, an example is attached below; for some reason I can't post attachments to this forum.

I then want to do a similar calculation for the 'Fix SLA' calculated against the 'Date Service Completed' field, based on the same criteria detailed above. E.g. if there is a date present that exceeds the Fix SLA Target Date the result should be failed, if there is a date that is within the Fix SLA it should pass, if there is no Fix SLA Target Date data it should read 'No Measurable SLA' and if there is no Service Date Completed date but today's date/time exceeds the Fix SLA Target Date it should again be a 'Failed'.

Hopefully this makes sense and someone can help Happy to receive any of the much appreciated assistance this forum has rendered to me in the past.

Regards

Ian

[TABLE="width: 597"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Response SLA Target Date[/TD]
[TD]Date Work Started[/TD]
[TD]Fix SLA Target Date[/TD]
[TD]Date Service Completed[/TD]
[TD]Response RESULT[/TD]
[/TR]
[TR]
[TD="align: right"]14 Apr 18 03:53:00[/TD]
[TD] [/TD]
[TD="align: right"]15 Apr 18 01:53:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]14 Apr 18 01:32:00[/TD]
[TD="align: right"]14 Apr 18 09:55:00[/TD]
[TD="align: right"]14 Apr 18 23:32:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 23:51:00[/TD]
[TD] [/TD]
[TD="align: right"]14 Apr 18 21:51:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 22:26:00[/TD]
[TD] [/TD]
[TD="align: right"]20 Apr 18 18:26:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 21:30:00[/TD]
[TD] [/TD]
[TD="align: right"]20 Apr 18 17:30:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 21:12:00[/TD]
[TD] [/TD]
[TD="align: right"]20 Apr 18 17:12:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 21:10:00[/TD]
[TD] [/TD]
[TD="align: right"]20 Apr 18 17:10:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 19:07:00[/TD]
[TD] [/TD]
[TD="align: right"]14 Apr 18 17:07:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
[TR]
[TD="align: right"]13 Apr 18 21:06:00[/TD]
[TD] [/TD]
[TD="align: right"]20 Apr 18 17:06:00[/TD]
[TD] [/TD]
[TD]Fail[/TD]
[/TR]
</tbody>[/TABLE]

 

[Max1616]

I think I'm confused as to how many formulas you want? Do you want two formulas? One for Response and one for Fix? If so then use these:
Response: =IF(A2="","No Response Measure",IF(AND(B2="",A2<TODAY()),"Fail",IF(B2>A2,"Fail","Pass")))
Fix: =IF(C2="","No Response Measure",IF(AND(D2="",C2<TODAY()),"Fail",IF(D2>C2,"Fail","Pass")))

If you want one formula to check if a fail occurs:
=IF(OR(IF(A2="","No Response Measure",IF(AND(B2="",A2<TODAY()),"Fail",IF(B2>A2,"Fail","Pass")))="Fail",IF(C2="","No Response Measure",IF(AND(D2="",C2<TODAY()),"Fail",IF(D2>C2,"Fail","Pass")))="Fail"),"Fail","Pass")

 

[ijheyburn]

Hi Max & thank you.

I want 2 x formulas, one to check the response SLA measure and the other for the Fix measure.

However when I try to use either of the formulas you've suggested I get the 'There's a problem with this formula' message. FYI in the case of the response my data is in columns O & P, which I have amended within your suggested formula but as I say an error message occurs. Same issue for the 'Fix' formula which I have also amended to my data columns Q & R.

Thanks for your help

Regards

Ian

 

[Max1616]

I forgot that this site removes stuff in between the greater than/less than signs when you type them out. I'm not entirely sure how to get those characters to show up properly and not remove all the text between them.

 

[Joe4]

I forgot that this site removes stuff in between the greater than/less than signs when you type them out. I'm not entirely sure how to get those characters to show up properly and not remove all the text between them.

Simply places spaces on both sides of any < or > signs, i.e.
3 < 5
7 > 3