Expressão para Calcular dias que faltam para o aniversario de clientes

[Edmundo]

Bom dia Comunidade,

Estou precisando de uma expressão para calcular quantos dias faltam para o aniversario do cliente, baseado na data de Nascimento. Alguma pessoa pode me auxiliar nessa expressão?

[Suvrutt_Gurjar · Accepted solution]

Please try 

IF(NUMBER(TEXT([Date_of_Birth],"MMDD"))>=NUMBER(TEXT(TODAY(),"MMDD")),

HOUR(EOMONTH([Date_of_Birth],(YEAR(TODAY())-YEAR([Date_of_Birth]))*12-1)+DAY([Date_of_Birth])-TODAY())/24,

HOUR(EOMONTH([Date_of_Birth],((YEAR(TODAY()))+1-YEAR([Date_of_Birth]))*12-1)+DAY([Date_of_Birth])-TODAY())/24

)

In the above expression, if the day /month of birth is less than today ( for example today is 2nd August , so all birth day/month less than today - example birthday date 31st June 2000, 18th April 1996 and so on) it will calculate days for birthday for next year (2024) because current year's birthday has already happened for those users.

If the day/month is higher than today 2nd August) , for example 10th August 1985,  17th September 1995 and so on, the expression will calculate the days to birthday from today in the current year (2023) as the birthday has yet to take place for those users in the current year.

Please test well.

[Edmundo]

Obrigado prezado Suvrutt, a expressão deu certo. Agradeço novamente.

[Suvrutt_Gurjar]

You are welcome.

The above expression can be made further compact because the IF() condition checks if the day/month are less or greater than day/month today and then basically only changes from YEAR(TODAY()) to YEAR(TODAY())+1. Other computation is identical.

Will post if I can test the more compact version.

Edit: Below is a shorter version of the expression.  Please test well if you will use it. I have of course tested on a handful of test cases.

HOUR( 
      (EOMONTH([Order Date],(
                               IF(NUMBER(TEXT([Order Date],"MMDD")) 
                                  >=NUMBER(TEXT(TODAY(),"MMDD")),   
                                    YEAR(TODAY()),
                                    (YEAR(TODAY())+1)
                                  )
                               -YEAR([Order Date]))*12-1
                 )+DAY([Order Date]))-TODAY()
                                                
     )/24