Solved Working out a previous date using criteria with If Statement

September 12, 2018 at 06:27:11
Specs: Windows 7
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


See More: Working out a previous date using criteria with If Statement

Reply ↓  Report •

#1
September 12, 2018 at 07:04:19
✔ Best Answer
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 not 23/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


Reply ↓  Report •

#2
September 12, 2018 at 07:16:25
Brilliant. It's exactly what I wanted.

Top Man.

Keep up the good work. :-)))


Reply ↓  Report •
Related Solutions


Ask Question