# Date formula working for half of rows

✭✭

I have a columns for expiration date, 6 months prior to exp date, and 3 months prior to exp date. Formula for 6 months prior is:

=IFERROR(IFERROR(DATE(YEAR([Expiration Date]@row), MONTH([Expiration Date]@row) - 6, DAY([Expiration Date]@row)), DATE(YEAR([Expiration Date]@row) - 1, MONTH([Expiration Date]@row) + 6, DAY([Expiration Date]@row))), " ")

Formula for 3 months prior is:

=IFERROR(IFERROR(DATE(YEAR([Expiration Date]@row), MONTH([Expiration Date]@row) - 3, DAY([Expiration Date]@row)), DATE(YEAR([Expiration Date]@row) - 1, MONTH([Expiration Date]@row) + 3, DAY([Expiration Date]@row))), " ")

Of the 250 rows in the sheet, 100 times the 3 months prior to exp date calculates incorrectly to 9 months prior, the other dates calculate correctly.

i.e. Exp date = 7/5/27 ; 6 months prior = 1/5/27; 3 months prior = 4/5/27

then I will get Exp Date = 2/3/26; 6 months prior = 8/3/25; 3 months prior = 5/3/25 Should be 11/3/25

Tags:

• ✭✭✭✭✭

Can you use 180 for 6 months and 90 for 3 months?

=[Date]@row - 180

=[Date]@row - 90

• ✭✭

The same rows that errored previously continue to calculate incorrectly. I updated the formula for the 3 months to =IFERROR([Expiration Date]@row - 91, " "). The dates for the rows in error before now have dates that are 275 days previous instead of 91.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!