Help

Re: Last 3 Month Formula

Solved
Jump to Solution
1019 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Drew_Nemer
8 - Airtable Astronomer
8 - Airtable Astronomer

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.

1 Solution

Accepted Solutions
Kamille_Parks
16 - Uranus
16 - Uranus

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.

See Solution in Thread

1 Reply 1
Kamille_Parks
16 - Uranus
16 - Uranus

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.