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

melissagxpo

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.

KPH

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

KPH

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

Thank you!! This was very helpful!

KPH

No problem, glad it helped.

melissagxpo

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

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