Can you please help? I'm looking to work out the date using a fixed date going backwards with the following criteria;

Criteria High (3 months)

Medium (6 months)

Low (12 months)For e.g. if I have a fixed date of 31/12/2018 and it has a criteria of "medium" I need a formula to give me a previous date. The formula should give me the answer of 23/6/2018.

Also, I need the same formula to give me criteria of "High" and "Low" too.

I'm currently using this formula but I need it for the 3 criteria's.

=IF(F3<>"Medium",F3-180)

F3 being the fixed date.

Thanks in advance.

message edited by kamranosman

Since you did not provide any cell references, we do not know the layout of your data. I'll assume (dangerous) that your data looks like this: A B 1 31/12/2018 Medium

If that is the case, subtracting 3, 6 or 12 months can be done using this formula:=IF(B1="High",EDATE(A1,-3),IF(B1="Medium",EDATE(A1,-6),EDATE(A1,-12)))

However, something in your post doesn't make sense. You said:

if I have a fixed date of 31/12/2018 and it has a criteria of "medium"...The formula should give me the answer of 23/6/2018.6 months prior to 31/12/2018 is

not23/6/2018. 6 months prior to 31/12/2018 is 30/6/2018.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Brilliant. It's exactly what I wanted. Top Man.

Keep up the good work. :-)))

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History