Can someone help me come up with a function to calculate a date 1 month ahead?

Options

For example, in column A I have the date 1/22/23. Now in column B I want it to automatically calculate 2/22/23. I know how to add 30 days to a date in a formula but since not all months have 30 days I want it to show the exact date in the next month.

Tags:

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @melissagxpo

    I would create a date using the DATE function and specify each part of it using the column A date, adding one to the month.

    =DATE(YEAR([Column A]@row), MONTH([Column A]@row) + 1, DAY([Column A]@row))

    This works for all months except December - you can't add 1 month to December. But you can cheat by wrapping the formula in an IFERROR and creating a date with Year +1 and month of January when the error occurs.

    =IFERROR(DATE(YEAR([Column A]@row), MONTH([Column A]@row) + 1, DAY([Column A]@row)), DATE(YEAR([Column A]@row) + 1, 1, DAY([Column A]@row)))



Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @melissagxpo

    I would create a date using the DATE function and specify each part of it using the column A date, adding one to the month.

    =DATE(YEAR([Column A]@row), MONTH([Column A]@row) + 1, DAY([Column A]@row))

    This works for all months except December - you can't add 1 month to December. But you can cheat by wrapping the formula in an IFERROR and creating a date with Year +1 and month of January when the error occurs.

    =IFERROR(DATE(YEAR([Column A]@row), MONTH([Column A]@row) + 1, DAY([Column A]@row)), DATE(YEAR([Column A]@row) + 1, 1, DAY([Column A]@row)))



  • melissagxpo
    Options

    Thank you!! This was very helpful!

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    No problem, glad it helped.

  • melissagxpo
    Options

    I ran into another issue where if the cell is blank then it returns the following error message. #INVALID DATA TYPE

    Any ideas on how to fix this when the date cell is blank? I just want it to return a blank cell.

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    You can wrap the whole formula in an IF(ISBLANK)

    =IF(ISBLANK([Column A]@row), "", IFERROR(DATE(YEAR([Column A]@row), MONTH([Column A]@row) + 1, DAY([Column A]@row)), DATE(YEAR([Column A]@row) + 1, 1, DAY([Column A]@row))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!