Subtracting months from a Date

dheerema@e15group.com

Hello,

I found the below formula that works to subtract months from a date field, however it only works when its going into another year. If its within the same year it does not work and receive an Incorrect Argument Error.

=IF((MONTH([Day 1 Date (manually add)]@row) - 1) < 1, DATE(YEAR([Day 1 Date (manually add)]@row) - 1, MONTH([Day 1 Date (manually add)]@row) - 1 + 12, DAY([Day 1 Date (manually add)]@row)), DATE(YEAR([Day 1 Date (manually add)]@row), MONTH([Day 1 Date (manually add)]@row) - 1, DAY()))

The Date field I am subtracting from is titled "Day 1 Date (manually add)"

Any help would be appreciated!

Devin Lee

@dheerema@e15group.com

Try this

=IF((MONTH([Day 1 Date (manually add)]@row) - 1) < 1, DATE(YEAR([Day 1 Date (manually add)]@row) - 1, MONTH([Day 1 Date (manually add)]@row) - 1 + 12, DAY([Day 1 Date (manually add)]@row)), DATE(YEAR([Day 1 Date (manually add)]@row), MONTH([Day 1 Date (manually add)]@row) - 1, DAY([Day 1 Date (manually add)]@row)))

dheerema@e15group.com

Thank you that works for within the same year, but looks like it might affect the year when going between years. Did I update one of the numbers incorrectly? When I updated the "- 3 + 12" it went back 3 years.

Or is there a formula that works for both within the same year and a different year?

=IF((MONTH([Day 1 Date (manually add)]@row) - 3) < 1, DATE(YEAR([Day 1 Date (manually add)]@row) - 3, MONTH([Day 1 Date (manually add)]@row) - 3 + 12, DAY([Day 1 Date (manually add)]@row)), DATE(YEAR([Day 1 Date (manually add)]@row), MONTH([Day 1 Date (manually add)]@row) - 3, DAY([Day 1 Date (manually add)]@row)))

I updated all the "1" to "3"

William Coz

@dheerema@e15group.com and @Devin I am actually wondering the same question, is there something that works for the same year and a different year as well? The original formula works great for selecting changing dates in a different year but stops working in the same year (2022).