Discussions

Expand all | Collapse all

Need Help with Annual Date Formula

  • 1.  Need Help with Annual Date Formula

    Posted 08-10-2017 14:49
    I am trying to create a formula that references a date of birth field (Date), and acts as a countdown until that date occurs again next. For instance, if someone has a Date of Birth of 06-09-1983, I want to know how many days until 06-09 occurs again.


  • 2.  RE: Need Help with Annual Date Formula

     
    Posted 08-10-2017 15:56
    For a formula duration field:

    var date thisyear = Date(Year(Today()),Month([Birth Date]),Day([Birth Date]));
    var date nextyear = Date(Year(Today()) + 1,Month([Birth Date]),Day([Birth Date]));

    If($thisyear >= Today(),
      $thisyear - Today(),
      $nextyear - Today())


  • 3.  RE: Need Help with Annual Date Formula

    Posted 08-10-2017 16:32
    Thank you Ken! Is there by chance an addition that I can make to the formula to change the font color to red if the number it generates is less than 90?


  • 4.  RE: Need Help with Annual Date Formula

     
    Posted 08-10-2017 19:30
    You would have to use a formula text field for that:

    var date thisyear = Date(Year(Today()),Month([Hire Date]),Day([Hire Date]));
    var date nextyear = Date(Year(Today()) + 1,Month([Hire Date]),Day([Hire Date]));
    var number dayscount = If($thisyear >= Today(),
        ToDays($thisyear - Today()),
        ToDays($nextyear - Today()));

    If($dayscount <= 90,
      "<span style=\"color:red;\">" & ToText ($dayscount) & "</span>",
      "<span style=\"color:black;\">" & ToText ($dayscount) & "</span>")

    You lose some filtering by numeric ability using this method, but you could display the "red" records by setting a filter on this field that contains a "red" value.