Forum Discussion

ToddMolino's avatar
ToddMolino
Qrew Cadet
5 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 ...
Formulas and functions
JustinTorrence's avatar
JustinTorrence
Qrew Cadet
5 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
    5 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
      5 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
      ------------------------------