# Formula for [date]+1 month?

Options

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?

• ✭✭✭✭✭✭
Options

Try this...

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

• ✭✭✭✭✭✭
Options

Try this...

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

• edited 03/30/21
Options

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...?

• ✭✭✭✭✭✭
Options

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

• Options

Hello,

continuing this thread because i need help with something similar.

I am trying to count one month from a particular date and need help with the formula.

for example the move in date is 7/29/2022 I want Smartsheet to automatically count 1 month from that date as the due date in a separate column? how do i go about doing that?

• ✭✭✭✭✭✭
Options

@Khanambano You would use that first formula listed in the "Accepted Answer".

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!