Adding 1 month to another date

dave.mcpherson56751

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

Paul Newcome

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

L_123

=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

dave.mcpherson56751

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

L_123

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

dave.mcpherson56751

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!

Appreciate your help

Dave

L_123

=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?

dave.mcpherson56751

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

Paul Newcome

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

dave.mcpherson56751

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

Paul Newcome

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?

dave.mcpherson56751

Month ahead formala including Sunday.docx

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

dave.mcpherson56751

Hi Paul

That might be better?

sorry

Dave

Paul Newcome

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

dave.mcpherson56751

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

Paul Newcome

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

dave.mcpherson56751

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