Formula for [date]+1 month?

03/28/21
Accepted

Hi all,

If I was using excel, I'd use:

'=DATE(YEAR(\$A1),MONTH(\$A1)+1,DAY(\$A1))

However the equivalent smartsheet formula...

'=DATE(YEAR([Column2]12), MONTH([Column2]12) + 1, DAY([Column2]12))

...doesn't seem to be very "smart" at all, because it gets to December and can't handle the concept of rolling over into the next year the way excel does.

I've seen workarounds with lots of IF statements, but that's super unwieldy. Is there a more elegant solution?

• Try this...

IFERROR(DATE(YEAR([Column2]12), MONTH([Column2]12) + 1, DAY([Column2]12)), DATE(YEAR([Column2]12) + 1, 1, DAY([Column2]12)))

thinkspi.com

• Try this...

IFERROR(DATE(YEAR([Column2]12), MONTH([Column2]12) + 1, DAY([Column2]12)), DATE(YEAR([Column2]12) + 1, 1, DAY([Column2]12)))

thinkspi.com

• Thanks for this - works well.

Next question; if I edit this formula to try and get [date] - 1 month, it doesn't work for some reason.

For eg, I have 30/03/2021 in [Column2]13 , why is....

'=IFERROR(DATE(YEAR([Column2]13), MONTH([Column2]13) - 1, DAY([Column2]13)), DATE(YEAR([Column2]13) - 1, 1, DAY([Column2]13)))

...returning 02/03/2021?

'=DATE(YEAR([Column2]13), MONTH([Column2]13) - 1, DAY([Column2]13))

...does it too. 😫

ETA: I think this is a February issue, as it seems to work ok for other months...?

• If you are just trying to subtract one month, try this one...

=IFERROR(DATE(YEAR([Column2]13), MONTH([Column2]13) - 1, DAY([Column2]13)), DATE(YEAR([Column2]13) - 1, 12, DAY([Column2]13)))

thinkspi.com