Subtracting months from a Date

11/29/21
Answered - Pending Review

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!

Answers

  • Devin LeeDevin Lee ✭✭✭

    @[email protected]

    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)))

  • 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"

Sign In or Register to comment.