Forum Discussion

KeithHendrick's avatar
KeithHendrick
Qrew Member
3 years ago

Reminder Notification for Upcoming Birthdays

We want to know when an employee's birthday is upcoming.  We capture their DOB in a date field, so how do I get that month/day information in a format that I can use in a Reminder notification?

------------------------------
Keith Hendrick
------------------------------
  • rbuschmeyer's avatar
    rbuschmeyer
    Qrew Trainee
    3 years ago
    I actually have this very thing in an app I built for another entity:

    First, I built a report that displays "Birthdays (Next Month)", then I created a Quickbase Notification that sends the report to a specified user on the 15th day of each month.
    The report is built off of the Birthday field (Formula Date) and an "Age (Now)" field (formula-numeric).  "Birthdate" field below is a standard Date field.

    Birthday Formula:
    AdjustYear([Birthdate], [Age (Now)]+1)

    Age Now Formula:
    //AGE and decimal months

    // replace the [DOB] field with your date of birth field

    var date DOB = [Birthdate];

    var number Years =

    Year(Today())-Year($DOB)
    -
    If(
    Month(Today())<Month($DOB)
    or
    (Month(Today())=Month($DOB) and Day(Today())<Day($DOB)),1,0);

    var number MonthsDifference = Month(Today()) - Month($DOB) ;
    var number Months = If($MonthsDifference >= 0, $MonthsDifference, 12 + $MonthsDifference);

    Round($Years + $Months/12,0.1)

    Hope this helps.

    ------------------------------
    Ryan Buschmeyer
    ------------------------------
    • KeithHendrick's avatar
      KeithHendrick
      Qrew Member
      3 years ago
      Thanks, Ryan.

      ------------------------------
      Keith Hendrick
      ------------------------------
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    3 years ago
    Here is an example of a formula Date Field which will calculate an employees Next anniversary of their hire date. You could just adapt it for your purposes

    var date HireDate=[Hire Date];
    var date Datetest = Date(Year(Today()),Month($HireDate),Day($HireDate));

    IF($DateTest >= Today(), $DateTest, AdjustYear($DateTest,1)

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • AUshYC's avatar
      AUshYC
      Qrew Trainee
      15 hours ago

      Hi All, 

      Thanks for explaining how to do this! I've attempted to copy the formula you've shared to get my birthdays in a month/day format. I keep getting a syntax error reporting a missing parentheses bracket. Here's what I have for my formula. Would you happen to have any idea what I'm doing incorrectly? 

      var date Birthday=[Date of Birth];
      var date Datetest = Date(Year(Today()),Month([Date of Birth]),Day([Date of Birth])
      IF($DateTest >= Today(), $DateTest, AdjustYear($DateTest,1))

       

      Thanks in advance! :)

      -Abby 

      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        15 hours ago

        try this fix.  needs an extra closing ) at the end of the 2nd line.

         

        var date Birthday=[Date of Birth];
        var date Datetest = Date(Year(Today()),Month([Date of Birth]),Day([Date of Birth]))
        IF($DateTest >= Today(), $DateTest, AdjustYear($DateTest,1))

    • KeithHendrick's avatar
      KeithHendrick
      Qrew Member
      3 years ago
      Worked perfectly. Thanks!

      ------------------------------
      Keith Hendrick
      ------------------------------