Adding 1 month to another date

Paul Newcome · 02/14/20

I just looked at it again. Total "duh" moment on my part. I wrote in the formula to add 2 to a weekday of 6 and 1 to a weekday of 7 thinking weekend being end of week. Duh. Saturday is 7 and Sunday is 1.

I am sorry about that. Here it is corrected...

=IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row))) + IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 7, 2, IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 1, 1))

dave.mcpherson56751 · 02/14/20

Hi Paul

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

Have a lovely weekend

Dave

Paul Newcome · 02/14/20

Happy to help! 👍️

TM123 · 11/10/20

https://community.smartsheet.com/discussion/comment/240086#Comment_240086

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 · 11/10/20

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

L_123 · 11/10/20

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 · 11/10/20

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

Adding 1 month to another date

Answers

Help Article Resources

Categories