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?

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

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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

Answers

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    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

  • Liz LomaxLiz Lomax
    edited 03/30/21

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

    thinkspi.com

Sign In or Register to comment.