Trouble with long date formula

arw88

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

Nick Korna

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!

arw88

Here is a screenshot if helpful

Nick Korna

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!

arw88

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!