Cant add 3 months onto a date in the next year when the date is between October and December.
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.
Answers
-
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
-
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)))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
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)))
-
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
-
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!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
@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))), "")
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!