Days Til Birthday

[Leoff]

Hi All,

 

As the New Year approaches I have a question as to how I can get positive numbers (day count) for the following situation...

 

...I use a date field set as 'DateOfBirth'. Then I have a calculation field called 'DateOfBirth_Reminder' set as a calculation of:

'DayOfYear ( DateOfBirth )-DayOfYear ( Today )' where I do not store the results.

 

I seem to get negative days rather than a positive count until the next birthday...how can I get the number of days until the next birthday?

 

Much thanks!

 

Leon

[aaa]

It seems that this works

Case(Date(Month(DateBirth),Day(DateBirth),Year(Today)) >= Today,

Date(Month(DateBirth),Day(DateBirth),Year(Today)),

Date(Month(DateBirth),Day(DateBirth),Year(Today)+1))-Today

[fossil]

Originally posted by Leoff:

[qb] I seem to get negative days rather than a positive count until the next birthday...how can I get the number of days until the next birthday?

[/qb]

I'm a little late on this topic, but here is the formula I came up with to get # of days until next Birthday:

 

If(DayofYear(Today)  <=  DayofYear(Birthday), DayofYear(Birthday)  - DayofYear(Today), (DayofYear(Date( 12 , 31 , Month(Today)))) - DayofYear(Today) + (DayofYear(Date(Month(Birthday), Day(Birthday), Year(Birthday) +1))))  

Breaking it down:

 

If a person has not yet had a Birthday this year (or today is their birthday):

 If(DayofYear(Today)  <=  DayofYear(Birthday) 

Then Subtract the Day of Year of Today from the Day of the Year of their Birthday.

 

DayofYear(Birthday)  - DayofYear(Today)  

Else Add number of days left in this year + the number of days in the following year until their birthday.

 

(DayofYear(Date( 12 , 31 , Month(Today)))) - DayofYear(Today) + (DayofYear(Date(Month(Birthday), Day(Birthday), Year(Birthday) +1))))