IF statement with dates

hi there,

I am trying to get excel to minus 3 months from a date IF a condition is met. the formula is =IF(ISNUMBER(SEARCH("STD",G4)), DATE(DAY(J4),MONTH(J4)-3,YEAR(J4)), "No") where column J is set to a short date with day/month/year format. It comes back with #Value! anyone any ideas where I am going wrong ?

Hi
Try this

Formula:

Please Login or Register  to view this content.

Note:DATE(year, month, day)

yep J is set at date and has 28/2/2018 written in it

still get #value!

See the file

Thank you Solved it.