Age Eligibilty

[mandypascual]

Hi! I am unsure if it is possible to create a formula to make this work for my school, please help!

I have a base created to track all applicants. We offer 5 trade classes each held 3 times a year that have a minimum age requirement; age 16yrs and 8mths (Culinary), age 17yrs (Welding and Phlebotomy), age 17yrs and 6mths (Heavy Equipment and Electrical). Currently I have a formula for DOB and age as of the current date. I would like a way to populate which upcoming class the applicant will first be eligible for. e.g. Applied for Culinary Arts, will be age eligible starting with our fall class in October of 2024. Right now I am plugging each DOB into an age calculator based on our start dates and selecting a drop down myself but would love if I could make this work with a formula in Airtable! Thanks!

[TheTimeSavingCo]

Yeap, I think it should be doable:

Link to base

DATEADD(
  DOB,
  SWITCH(
    {TRADE APPLIED},
    "Culinary", 200,
    "Welding", 204,
    "Phlebotomy", 204,
    "Heavy Equipment", 210,
    "Electrical", 210
  ),
  'months'
)

I'm not too sure how you're defining the cycles though, if you could let me know which months fall into which cycles I can see what I can do!

[mandypascual]

Wow, this is great! Thank you so much! I attached a photo showing our cycle start and end dates. We do not have dates set for 2025 yet but they will be very smiliar to 2024.

[TheTimeSavingCo]

Thanks!  I've updated the base above and it now has a "Cycle" field which I think does what you want:

And the logic is:
 - If "DOB + Age Requirement based on trade" is before the start of the first cycle, use the first cycle
 - If "DOB + Age Requirement based on trade" is after the start of the first cycle and before the start of the second cycle, use the second cycle
 - If "DOB + Age Requirement based on trade" is after the start of the second cycle and before the start of the third cycle, use the third cycle

I've pasted the formula I used below, and you'll need to create the rest of the classes and dates as I only created it for "Culinary"

IF(
  {TRADE APPLIED} = "Culinary",
  IF(
    IS_BEFORE(
      {DOB + Age Requirement based on trade},
      DATETIME_PARSE(
        "8 Jan 2024",
        "DD MMM YYYY"
      )
    ),
    "C24-1"
  ) &
  IF(
    AND(
      OR(
        IS_AFTER(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "8 Jan 2024",
            "DD MMM YYYY"
          )
        ),
        IS_SAME(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "8 Jan 2024",
            "DD MMM YYYY"
          )
        )
      ),
      OR(
        IS_BEFORE(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "6 May 2024",
            "DD MMM YYYY"
          )
        ),
        IS_SAME(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "6 May 2024",
            "DD MMM YYYY"
          )
        )
      )
    ),
    "C24-2"
  ) &
  IF(
    AND(
      OR(
        IS_AFTER(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "6 May 2024",
            "DD MMM YYYY"
          )
        ),
        IS_SAME(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "6 May 2024",
            "DD MMM YYYY"
          )
        )
      ),
      OR(
        IS_BEFORE(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "3 Sep 2024",
            "DD MMM YYYY"
          )
        ),
        IS_SAME(
          {DOB + Age Requirement based on trade},
          DATETIME_PARSE(
            "3 Sep 2024",
            "DD MMM YYYY"
          )
        )
      )
    ),
    "C24-3"
  ) 
)