Last 3 Month Formula

[Drew_Nemer]

I have a field that is called Re-Exam date. Each Re-exam date is the first of a month in a year.

I want to count the total number records of the Current Re-Exam Date (the current month), Next Re-Exam Date (Next month), and the previous month Re-Exam Date.

So let’s say a person had:
20 Re-Exams for the Re-Exam Date of 4/1/2022
14 Re-Exams for the Re-Exam Date of 5/1/2022
8 Re-Exams for the Re-Exam Date of 6/1/2022.

I can total these in a formula to 42.

And when the current month is 6/1/2022, the window shifts so I get the total of 5/1/2022, 6/1/2022, and 7/1/2022.

[Kamille_Parks · Accepted solution]

Are each “re-exams” their own row in a table? Is each re-exam linked to a person?

If so, each re-exam record could have a set of formulas that ask if its this month, next month, or the month after.

IF(
  DATETIME_FORMAT({Re-Exam Date}, "M/YYYY") = DATETIME_FORMAT(TODAY(), "M/YYYY"),
  "yes", 
  "no"
)

and for the future months follow this format (and adjust the 1 to 2 to find 2 months from now)

IF(
  DATETIME_FORMAT({Re-Exam Date}, "M/YYYY") = DATETIME_FORMAT(DATEADD(TODAY(), 1, "month"), "M/YYYY"),
  "yes", 
  "no"
)

If you want this in all one formula, it would look like:

IF(
  OR(
    DATETIME_FORMAT({Re-Exam Date}, "M/YYYY") = DATETIME_FORMAT(TODAY(), "M/YYYY"),
    DATETIME_FORMAT({Re-Exam Date}, "M/YYYY") = DATETIME_FORMAT(DATEADD(TODAY(), 1, "month"), "M/YYYY"),
    DATETIME_FORMAT({Re-Exam Date}, "M/YYYY") = DATETIME_FORMAT(DATEADD(TODAY(), 2, "month"), "M/YYYY"),
  ),
  "yes", 
  "no"
)

From there, the Person record could use a Count field(s) with a condition applied for if your formula field(s) = yes. That will give you the count of how many re-exam dates are linked to that person where it happens in the next few months.