Forum Discussion

ToddMolino's avatar
ToddMolino
Qrew Cadet
4 years ago

Birthdate Year Falling on a Multiple of 5 Checkbox

Trying to create a formula checkbox that is checked if a person's age will be a multiple of 5 and they are within 6 months of their birthday month. Example, Let's say it's May 1st, 2021 now, and John Doe was born on 7/1/2001. He will be 20 years old in a couple of months and 20 is a multiple of 5 and July, (his birthday month) is within 6 months of today, (May 1st), so his checkbox should get checked. If his birthday month was in December, then it would not get checked until June 1st, 2021 and will not get checked for another 5 years.
I currently have a Birthday Month field and Current Age field. 
I suppose I would then need to create a means of unchecking the box after a year, maybe with a pipeline. I'll worry about that piece later. 
This has thrown me for a loop. Any help would be greatly appreciated! Mark? Anyone? 
This is by far the craziest request I've been given. Thanks again for any takers.....

------------------------------
Todd
------------------------------
Formulas and functions
  • JustinTorrence's avatar
    JustinTorrence
    Qrew Cadet
    4 years ago

    I think I've got something for you. This field would be a formula checkbox. No need to use pipelines to check/uncheck:

    You should be able to replace the "Birthdate" variable at the top with your equivalent field.

    I tried to break it out as much as possible so that you could see all the steps it takes to get there.

    var date birthday = [Birthdate];

// Find the date of their next birthday
var date birthdayThisYear = Date(Year(Today()), Month($birthday), Day($birthday));
var date birthdayNextYear = Date(Year(Today()) + 1, Month($birthday), Day($birthday));
var date nextBirthday =
If(
    Today() > $birthdayThisYear, $birthdayNextYear,
    $birthdayThisYear
);

// Find the age they will be on their next birthday
var number ageNext =
Int(ToWeeks(Today() - $nextBirthday)/52);

// Determine if their next birthday will be a divisible of 5
var bool divisibleOfFive = 
Frac($ageNext / 5) = 0;

// Determine if their Birthday is within the next 6 months
var bool inNextSixMonths = AdjustMonth($nextBirthday, -6) < Today();


// Final Evaluation: Determine if their next birthday will be in 6 months and if it is a divisible of 5

$divisibleOfFive and $inNextSixMonths


    ------------------------------
    Justin Torrence
    Quickbase Expert, Jaybird Technologies
    jtorrence@jaybirdtechnologies.com
    https://www.jaybirdtechnologies.com/#community-post
    ------------------------------
    • ToddMolino's avatar
      ToddMolino
      Qrew Cadet
      4 years ago
      Thanks so much Justin! I'll give it a go and see how it works. I REALLY appreciate the help! Stay tuned.....

      Todd

      ------------------------------
      Todd
      ------------------------------
      • ToddMolino's avatar
        ToddMolino
        Qrew Cadet
        4 years ago
        Hi Justin, 
         I made just a few tweaks because it was giving me a false positive when their age was not going to be a multiple of 5 within the next 6 months. Here's what it looks like now. Thanks again! 

        var date birthday = [Birth Date];

        // Find the date of their next birthday
        var date birthdayThisYear = Date(Year(Today()), Month($birthday), Day($birthday));
        var date birthdayNextYear = Date(Year(Today()) + 1, Month($birthday), Day($birthday));
        var date nextBirthday = If(Today() > $birthdayThisYear, $birthdayNextYear, $birthdayThisYear);

        // Find the age they will be on their next birthday
        var number ageNext = [Current Age] + 1;

        // Determine if their next birthday will be a divisible of 5
        var number divisibleOfFive = Rem($AgeNext, 5);

        // Determine if their Birthday is within the next 6 months
        var bool inNextSixMonths = AdjustMonth($nextBirthday, -6) < Today();


        // Final Evaluation: Determine if their next birthday will be in 6 months and if it is a divisible of 5

        If(
        $divisibleOfFive = 0 and $inNextSixMonths, true, false
        )

        ------------------------------
        Todd
        ------------------------------