# Adding 1 month to another date

Hi,

Does anyone have a formula to automatically calculate 1 month forward from a date in another cell?

I have tried =MONTH(Due1,+1) but this returns #Date expected error. (Due1 is the column and cell i am trying to add the month to.

The above would be perfect but also if there is a genius way of the above calculation only being a working day?

Thank you for anyone who can help and taking the time to assist.

Dave

«1

• =IF(AND(WEEKDAY(Due@row) < 7, WEEKDAY(Due@row) > 1), DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row)))

will work at any day of the year. Let me know if it works for you

• Hi thank you for taking some time to look at this,

I still seem to be pulling though weekends?

Happy to share a sheet for you to look at if that helps?

Dave

• You mean if the resulting date is a weekday? Mine checked if the date in the due column was a weekday. This will post a blank if the resulting date is on a weekend.

=IF(AND(WEEKDAY(DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row))) < 7, WEEKDAY(DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row))) > 1), DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row)), "")

• Hi, Yes i am getting blanks with the above formula.

Would you have the formula to simply add a month and i will live with weekends and work around it.

Sound crazy but i cannot get it to simply add one month!

Dave

• =DATE(YEAR(Due@row), MONTH(DATE(YEAR(Due@row), MONTH(Due@row), 25) + 7), DAY(Due@row)))

This will return 1 month ahead. The correct version of the formula you were trying to do is

=month(Due@row)+1

But the issue with this formula is december

12 + 1 does not equal 1.

Can you tell me a little more about why my previous formula didn't fit your needs? I must be misunderstanding what output you want in relation to working days. Do you want it to return the next monday if the true value is on the weekend?

• Yes a Monday or Friday would be perfect if the value was on a weekend

• =IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))

This will give you the date for 1 month in advance and account for December going into January.

=IF(WEEKDAY(date value) = 6, 2, IF(WEEKDAY(date value) = 7, 1))

This will generate a 2 if the date is a Saturday, 1 if the date is a Sunday, and nothing for Monday through Friday.

If we drop our date value formula into the IF function, we will know whether we need to add 0, 1, or 2 days based on the weekday of the date formula.

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

Then we add this to the date formula's output to generate the date for 1 month later with the following Monday's date if it happens to fall on a Saturday or Sunday.

=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)))) = 6, 2, IF(WEEKDAY(IFERROR(DATE(YEAR(Due@row), MONTH(Due@row) + 1, DAY(Due@row)), DATE(YEAR(Due@row) + 1, 1, DAY(Due@row)))) = 7, 1))

• Hi Paul

thank you for taking the time to look into this, looks amazingly complicated.

I have added the formula but still return Sunday dates ( Saturdays no longer appearing)

any help appreciated

Dave

• Can you copy/paste the formula from your sheet to her and maybe provide a screenshot with a few dates that are working properly vs a few dates that are not?

• Hi Paul,

Bit techophobe with attachment but hopefully you can see the formula used in the cell where it pulls through a Sunday. Basically adding a month to the column date above.

Regards

Dave

• Hi Paul

That might be better?

sorry

Dave

• What is the formula in Row2, Row4, and Row5?

• Hi Paul,

The same formula but in row 2 it would point to Due 1 in all aspects. Row 4 would point at Due 3 e,t,c

Hope that helps

Regards

Dave

• That's how it SHOULD be. I am just trying to make sure that is how it ACTUALLY is.

• Hi Paul

Happy to share the sheet if that helps, but just double checked my self and all the formulas are from the row above. Starting date is Thursday 30th Jan and next month pulls through Sunday 1st March.

All very weird, but really appreciate your time looking into this

Dave

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!