# Need Help with Annual Date Formula

• 1
• Question
• Updated 2 years ago
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.
• 122 Points

Posted 2 years ago

• 1
• 200 Points
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())
• 122 Points
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?
• 200 Points
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.