Trouble with long date formula
Hi,
I am working on something that will tell staff when the open window for a visit is, based on the visit category.
Rule 1: Visits in the category of 1, 2, 3-5, 6-10, and 11+ years should be annually and the window is their DOB +/- 1 month. This portion of the formula works.
Rule 2: 3rd trimester visit should start 197 days from LMP and a 2nd trimester visit should start 98 days from LMP, this formula also works when combined with Rule 1.
Rule 1&2 Formula:
=IF(OR([Current Visit]@row = "3 to 5 Years", [Current Visit]@row = "6 to 10 Years", [Current Visit]@row = "11 to 17 Years", [Current Visit]@row = "1 Year", [Current Visit]@row = "2 Year"), IFERROR(DATE(YEAR(DOB@row) + [Visit Age]@row, MONTH(DOB@row) - 1, DAY(DOB@row)), DATE(YEAR(DOB@row), MONTH(DOB@row), 1)), IF([Current Visit]@row = "Tri3", LMP@row + 197, IF([Current Visit]@row = "Tri2", LMP@row + 98, "")))
Here is where I am having trouble
Rule 3: A 6 to 11 month visit should start 6 months from DOB. I have tried several different variations of formulas to add this in, but nothing is working. The closest I have got is this formula that always defaults to the 1st of the month instead of the day of DOB. I am appending the following to the above formula.
IF([Current Visit]@row = "6 to 11 Month", IFERROR(DATE(YEAR(DOB@row), MONTH(DOB@row) + 6, DAY(DOB@row)), DATE(YEAR(DOB@row) + 1, MONTH(DOB@row) - 6)), ""))))
When I add DAY(DOB@row) to the end, it has an error.
Kind of complicated, but any thoughts on where the issue with this final portion is? If nothing else I will add "days" since that is easier, but not quite as accurate as months. Sometimes 6 months is 180 days and sometimes 182 days. Thank you!!
Best Answer
-
Hi @arw88,
Your formula just needs a few tweaks with brackets (as a standalone, at any rate!) and adding a DAY function in the IFERROR portion:
=IF([Current visit]@row = "6 to 11 Month", IFERROR(DATE(YEAR(DOB@row), MONTH(DOB@row) + 6, DAY(DOB@row)), DATE(YEAR(DOB@row) + 1, MONTH(DOB@row) - 6, DAY(DOB@row))), "")
Sample output (dates in Euro format):
The DAY function missing is what is causing your dates to default to the 1st of the month where the DOB falls in the second half of the year.
Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!
Answers
-
Here is a screenshot if helpful
-
Hi @arw88,
Your formula just needs a few tweaks with brackets (as a standalone, at any rate!) and adding a DAY function in the IFERROR portion:
=IF([Current visit]@row = "6 to 11 Month", IFERROR(DATE(YEAR(DOB@row), MONTH(DOB@row) + 6, DAY(DOB@row)), DATE(YEAR(DOB@row) + 1, MONTH(DOB@row) - 6, DAY(DOB@row))), "")
Sample output (dates in Euro format):
The DAY function missing is what is causing your dates to default to the 1st of the month where the DOB falls in the second half of the year.
Hope this helps, but if I've misunderstood anything or you've any problems/questions then just let us know!
-
So far that looks like it did the trick! I was able to use your formula to add one final category as well and it also does not seem to have any errors so far 🤞. Really appreciate it, very helpful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!