Number of days between today and a birthday (this year)

HCF
Participant V

Can I calculate the number of days between a birthday and today when I know the birthdate (actual year)
I tried:
Date(Text([BirthDate],“MM/DD/” & Year(Today()))) - Today()
, but that does not work out.
Thanks for any help.

Solved Solved
0 4 237
1 ACCEPTED SOLUTION

I believe you are close but there are some parenthesis changes required so your basic expression could be rewritten as

DATE(TEXT([BirthDate],“MM/DD")& “/”&YEAR(TODAY()))-TODAY()

Please note minor but important changes in the expression construct above. The above expression will give duration in hours between two dates.

Please wrap it by HOUR() and divide by 24 to get the number of days.

HOUR( DATE(TEXT([BirthDate],“MM/DD")& “/”&YEAR(TODAY()))-TODAY() ) /24

The above will give the days between today and the birthday. However, if the birthday is in the past in this year, for example, if the above expression is tried today (22nd February for all birth days prior to 22nd Feb) it will return a negative number of days. Please handle that part of logic as per your requirement.
This wrapping by HOUR() is given in the Duration examples in the article below.

View solution in original post

4 REPLIES 4

Aurelien
Participant V

Hi

This article may help you:

You may want to take an eye on the formula TOTALHOURS, then divide by 24 ?

Maybe something like:

TOTALHOURS(
     IF(
          TODAY() < DATE(Substitute([birthDate], year([birthDate]), year(today()))),
          DATE(Substitute([birthDate], year([birthDate]), year(today()))),
          DATE(Substitute([birthDate], year([birthDate]), year(today()) + 1))
   )
     - 
     TODAY()
     )
/
24

?

I believe you are close but there are some parenthesis changes required so your basic expression could be rewritten as

DATE(TEXT([BirthDate],“MM/DD")& “/”&YEAR(TODAY()))-TODAY()

Please note minor but important changes in the expression construct above. The above expression will give duration in hours between two dates.

Please wrap it by HOUR() and divide by 24 to get the number of days.

HOUR( DATE(TEXT([BirthDate],“MM/DD")& “/”&YEAR(TODAY()))-TODAY() ) /24

The above will give the days between today and the birthday. However, if the birthday is in the past in this year, for example, if the above expression is tried today (22nd February for all birth days prior to 22nd Feb) it will return a negative number of days. Please handle that part of logic as per your requirement.
This wrapping by HOUR() is given in the Duration examples in the article below.

HCF
Participant V

Thanks a lot to both of you @Suvrutt_Gurjar and @Aurelien .
Both solutions work, however I prefer the one supplied above.

Top Labels in this Space