in between #...issue if statement

Hello

Having some trouble trying to get the if statement to work or not sure what direction to take....

basically, i have two criterias

1) # of days to complete - column J

2) service response time - column N

basically, days to complete is how long it took our company to complete the service in (actual), and service response time is agreement to do so (budgeted)...

the issue is, the service resposne time is in words like (3-5 days etc), so im not sure how to break that up as there are over thousands of lines.

i have attached a sample and highlighted in red, the issues (column O & R)

column O = simple "Yes" or "No" - if days to complete is within service time frame, then YES, else NO

column R = calculation - if days to compelete is outside of service response time (3-5 days) then max of 3-5 days minus days to compelte

i.e.
days to complete 3-5 days, service time 8 days
therefore = 8-5
=3 days

can someone pls help with this, been struggling hard, thank you.

cheers

You seem to have a data issue here. There is no standard to the data in column N so it is really not possible to separate the data either logically or through programming. Can I suggest changing the way you store the information that you store in the notes field of all stores into a minimum and a maximum number of days. It will create a much better data format for future improvements as well. Also if you need help in how to break that data up in a formula let me know.

hello there

thxs for your input

i was hoping, if we can ignore things such as "E2W on Tuesdays" etc and focus where it only says "3-5 days"....i was hoping that portion can at laest be broken out into to do the yes/no scenario and # of days over?

i appreciate your help, pls let me know, thxs!

Originally Posted by everythingneon

You seem to have a data issue here. There is no standard to the data in column N so it is really not possible to separate the data either logically or through programming. Can I suggest changing the way you store the information that you store in the notes field of all stores into a minimum and a maximum number of days. It will create a much better data format for future improvements as well. Also if you need help in how to break that data up in a formula let me know.

hey there

just wondering if you can give me an example of what you mean by "max and min # of days?"

thxs buddy

any solutions guys? thx you.

Hello NBVC

Currently the list only shows 3-5 days so it would be great if a formula can be worked out.

also, is it possible to show a "YES" when items also contain "E" in the start and "P" and blanks?

thx you for your help with this matter.