Cant add 3 months onto a date in the next year when the date is between October and December.

mike.thorpe17421

I am trying to add a column formula whereby a date is added in one column and in another column 3 months is added to the first sou,n's date. This works fine where the month is between January and September. However if 1 October 2022 is added the date should be 1 January 2023. It lists it as 1 October 2022. (the same). IF I add 1 September the date added is 1 December 2022 which is correct. The formula I have is as follows:

Reimbursement Recommended Date is the date it should add 3 months onto.

=IFERROR(IFERROR(DATE(YEAR([Reimbursement Recommended Date]@row), MONTH([Reimbursement Recommended Date]@row) + 3, DAY([Reimbursement Recommended Date]@row)), DATE(YEAR([Reimbursement Recommended Date]@row), MONTH([Reimbursement Recommended Date]@row), DAY([Reimbursement Recommended Date]@row))),"")

Can anyone confirm what I have missed or is wrong so it will go into the following year.

Christian G.

I think it is due to the fact that you are sending adding 3 month to the month 10, so the "Date(Y,M,D)" function receive Date(2022,13,D).

So the year is not updated because you are getting the year of the current "Reimbursement recommend date" without checking the rollover.

Can you add 90 days ?

=[Reimbursement Recommended Date]+90

Paul Newcome

Try this:

=IFERROR(DATE(YEAR([Date Column]@row), MONTH([Date Column]@row) + 3, DAY([Date Column]@row)), DATE(YEAR([Date Column]@row) + 1, MONTH([Date Column]@row) - 9, DAY([Date Column]@row)))

=IFERROR(DATE(YEAR([Reimbursement Recommended Date]@row), MONTH([Reimbursement Recommended Date]@row) + 3, DAY([Reimbursement Recommended Date]@row)), DATE(YEAR([Reimbursement Recommended Date]@row) + 1, MONTH([Reimbursement Recommended Date]@row) - 9, DAY([Reimbursement Recommended Date]@row)))

mike.thorpe17421

Thanks Paul.

The 2nd example works and adds 3 months and the following year but where there is no data it adds #INVALID DATA TYPE. This is because there is no data in the original filed. I have tried added ,"" at the end of the syntax but this doesn't work. See following string which works. Any thoughts where to add the element that lists it as blank if there is no data in the source field?

=IFERROR(DATE(YEAR([HTA Anticipated Approval Date]@row), MONTH([HTA Anticipated Approval Date]@row) + 3, DAY([HTA Anticipated Approval Date]@row)), DATE(YEAR([HTA Anticipated Approval Date]@row) + 1, MONTH([HTA Anticipated Approval Date]@row) - 9, DAY([HTA Anticipated Approval Date]@row)))

Christian G.

Add a verification to the cell before doing the addition

=if(not(Isblank([HTA Anticipated Approval Date]@row)),IFERROR(DATE(YEAR([HTA Anticipated Approval Date]@row), MONTH([HTA Anticipated Approval Date]@row) + 3, DAY([HTA Anticipated Approval Date]@row)), DATE(YEAR([HTA Anticipated Approval Date]@row) + 1, MONTH([HTA Anticipated Approval Date]@row) - 9, DAY([HTA Anticipated Approval Date]@row))),"")

If it is not blank, do the addition, print "" otherwise

Andrée Starå

Hi @mike.thorpe17421

I hope you're well and safe!

To add to Christian's excellent advice/answer.

Another option.

=IF([HTA Anticipated Approval Date]@row<>"", your formula

Would that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Paul Newcome

@mike.thorpe17421 Give this adjustment a try...

=IFERROR(IFERROR(DATE(YEAR([HTA Anticipated Approval Date]@row), MONTH([HTA Anticipated Approval Date]@row) + 3, DAY([HTA Anticipated Approval Date]@row)), DATE(YEAR([HTA Anticipated Approval Date]@row) + 1, MONTH([HTA Anticipated Approval Date]@row) - 9, DAY([HTA Anticipated Approval Date]@row))), "")