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