Adding 1 month to another date

2»

Answers

  • dave.mcpherson56751
    dave.mcpherson56751 ✭✭✭✭✭

    Hi Paul


    Brilliant, works a treat, thank you so much for your help.

    Have a lovely weekend

    Dave

  • TM123
    TM123 ✭✭✭

    Hi Paul, Any idea how to fix this formula when yo get to December/ end of the year. I'm getting #Incorrect Argument, - works perfectly fine until Dec20 needs to turn into Jan21, and the remainder of the lines are blocked. The formula above, the November 10, is a Today() formula.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @TM123 Are you just adding a single month to whatever date is above it?

  • L_123
    L_123 ✭✭✭✭✭✭

    yeah that's his issue. You need to find a random date in the next month and return the first based off that.

    =date(year(date(year(today()),month(today()),25)+7),month(date(year(today()),month(today()),25)+7),1)

    for example will always return the first day of the next month. I'm unaware of a more consistent shorter way to do this.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    If just adding one month and keeping the same date, then I you could go with...

    =DATE(YEAR([Savings Start Month]3) + IF(MONTH([Savings Start Month]3) = 12, 1), IF(MONTH([Savings Start Month]3) = 12, 1, MONTH([Savings Start Month]3) + 1), DAY([Savings Start Month]3))


    If looking for the 1st of next month, just replace the DAY function with 1.

    =DATE(YEAR([Savings Start Month]3) + IF(MONTH([Savings Start Month]3) = 12, 1), IF(MONTH([Savings Start Month]3) = 12, 1, MONTH([Savings Start Month]3) + 1), 1)


    Not sure how it compares with keystrokes/length of formula but seems to be about the same and (at least to me) reads a little more smoothly.


    Using TODAY instead of a cell reference:

    =DATE(YEAR(TODAY()) + IF(MONTH(TODAY()) = 12, 1), IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), DAY(TODAY()))


    =DATE(YEAR(TODAY()) + IF(MONTH(TODAY()) = 12, 1), IF(MONTH(TODAY()) = 12, 1, MONTH(TODAY()) + 1), 1)


    I feel like it is easier to read because it is a basic IF statement that drives the year and month.


    I personally tend to use an IFERROR statement to basically pull two separate DATE functions together.

    =IFERROR(DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1), DATE(YEAR(TODAY()) + 1, 1, 1))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!