Calculating past date from a January Birthday

arw_88

I thought I had this all figured out a while back, until diving back in now I notice that a handful of my dates are not working correctly. I figured out it is all of the rows where the person has a birthday in the first couple weeks of January.

I want to take the birthday and create a date range that a survey can be completed (Birth date +/- 30 days). My formula is

=IF(OR([Current Visit]@row = "3 to 5 Year", [Current Visit]@row = "6 to 10 Year", [Current Visit]@row = "11 to 17 Year", [Current Visit]@row = "1 Year", [Current Visit]@row = "2 Year"), IFERROR(DATE(YEAR(DOB@row) + [Year End Age]@row, MONTH(DOB@row) - 1, DAY(DOB@row)), DATE(YEAR(DOB@row), MONTH(DOB@row), 1)), , ""))

If anyone might know where I could fix the formula, that would be very helpful! Thank you in advance.

Paul Newcome

One month before would be:

=IFERROR(DATE(YEAR(End@row), MONTH(End@row) - 2, DAY(End@row)), DATE(YEAR(End@row) - 1, MONTH(End@row) + 10, DAY(End@row)))

One month after would be:

=IFERROR(DATE(YEAR(TODAY()) + IF(TODAY() > IFERROR(DATE(YEAR(TODAY()), MONTH(DOB@row) + 1, DAY(DOB@row)), DATE(YEAR(TODAY()) + 1, 1, DAY(DOB@row))), 1, 0), MONTH(DOB@row) + 1, DAY(DOB@row)), DATE(YEAR(TODAY()) + IF(TODAY() > IFERROR(DATE(YEAR(TODAY()), MONTH(DOB@row) + 1, DAY(DOB@row)), DATE(YEAR(TODAY()) + 1, 1, DAY(DOB@row))), 2, 1), 1, DAY(DOB@row)))

Paul Newcome

What exactly are you hoping the formula will output?

arw_88

Right now, my output columns are start date/end date. Example below.

A survey should be completed within +/- 1 month of their birthday. So Row 14 is correct - birthdate is Feb 2 and the start date is Jan 2 and the end date is Mar 1.

Everything below row 14 is incorrect because of their birthdays being in January.

Row 1 for example should be Start: 12/9/23; End: 2/08/24. The end date is coming out correct but the start date is defaulting to Jan 1 of their birth year.

Paul Newcome

One month before would be:

=IFERROR(DATE(YEAR(End@row), MONTH(End@row) - 2, DAY(End@row)), DATE(YEAR(End@row) - 1, MONTH(End@row) + 10, DAY(End@row)))

One month after would be:

=IFERROR(DATE(YEAR(TODAY()) + IF(TODAY() > IFERROR(DATE(YEAR(TODAY()), MONTH(DOB@row) + 1, DAY(DOB@row)), DATE(YEAR(TODAY()) + 1, 1, DAY(DOB@row))), 1, 0), MONTH(DOB@row) + 1, DAY(DOB@row)), DATE(YEAR(TODAY()) + IF(TODAY() > IFERROR(DATE(YEAR(TODAY()), MONTH(DOB@row) + 1, DAY(DOB@row)), DATE(YEAR(TODAY()) + 1, 1, DAY(DOB@row))), 2, 1), 1, DAY(DOB@row)))

arw_88

Thank you so much!!

I had a bit more logic in my formula based on another column, but was able to incorporate your formula and it is working great it seems!

One thing is that I never even thought of calculating the 'start date' from the 'end date' instead of both from DOB. That is quite a lot more straightforward I believe. Really appreciate it. ☺️

Paul Newcome

Happy to help. 👍️