Formula for [date]+1 month?

Liz Lomax

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?

Or can someone please advise how to request this gets developed?

Paul Newcome

Try this...

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

Paul Newcome

Try this...

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

Liz Lomax

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

Paul Newcome

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

Khanambano

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?

Paul Newcome

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