If/Then using dates

[MN_Excel]

I'm attempting to use a formula to calculate product expiration dates. If the date in a particular cell is before 5/1/11 I would like the expiration date to be the cell date plus 3 months. If it is after that date I would like the expiration date to be the cell date plus 6 month? For example, a cell with date 4/1/11 would have an expiration date of 7/1/11 while a cell with date of 6/1/11 would have a expiration date of 12/1/11. Any suggestions on how to write this formula?

Thanks in advance!

 

[VoG]

Try

=IF(A1<"5/11/11"+0,DATE(YEAR(A1),MONTH(A1)+3,"DAY(A1)),DATE(YEAR(A1),MONTH(A1)+6),DAY(A1)))

 

[Scott Huish]

If you are using a version of Excel prior to XL2007, you will need to load the Analysis Toolpak for this:

=EDATE(A1,IF(A1<"5/11/11"+0,3,6))

or

without it:

=DATE(YEAR(A1),MONTH(A1)+IF(A1<"5/11/11"+0,3,6),DAY(A1))

Format as date.

 

[MN_Excel]

Thank you very much....it worked perfectly!