Skip to main content

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.

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.

Reply


Terms & Conditions