Calculated field on birthdays

[bookish]

Hi! I have a formula that worked when 
date of birth - April 12
Start of activity - April 11
End of activity - April 13
The intention is for a venue of an activity to know who is any bday celebrant in that activity.
The formula did not work in this longer duration of an activity (May 16 to June 1)
date of birth - May 17
There could also be the case of a birthday celebrant of Dec 29 in a New Year activity Dec 26 to Jan 3
Hope you can help me by tweaking this
 

CASE

WHEN (datepart(month, [@field:Date_of_Birth]) >= datepart(month, [@field:START]) AND datepart(day, [@field:Date_of_Birth]) >= datepart(day, [@field:START]) AND datepart(month, [@field:Date_of_Birth]) <= datepart(month, [@field:Enddate]) AND datepart(day, [@field:Date_of_Birth]) <= datepart(day, [@field:Enddate]) )

THEN [@field:Date_of_Birth]

ELSE NULL

END

 

[PotatoMato]

Hi, @bookish.  We will have to include an additional condition and it will result in a long case statement since the start and end months aren't equal to the birth month. You can just use "BETWEEN" to shorten it.

CASE
  WHEN [@field:Birthday] BETWEEN [@field:Start_date] AND [@field:End_date]
  THEN 'Happy Birthday!!'
  ELSE 'Normal day'
END

 

-Potato

[bookish]

Thanks for replying @PotatoMato.  It did not work. I should have specified the YYYY in my original query.
date of birth - April 12, 1967
Start of activity - April 11, 2023
End of activity - April 13, 2023

[bookish]

I have decided to settle on the birth month instead (i.e. showing the birthday only when the month coincides with the months of the activity)
 

CASE

WHEN Month([@field:Date_of_Birth])  >= Month([@field:START]) AND Month([@field:Date_of_Birth])  <= MONTH([@field:Enddate]) 

THEN [@field:Date_of_Birth]

ELSE NULL

END

[CoopperBackpack]

Hello @bookish,

You may also test the following solution.

1) It is possible to calculate the date of birth in the year of activity.

Since in some cases, the activity may start in one year and end in the next year, we need to calculate the date of birth twice.

• BD in the year of StartDay

CONVERT(date, 
        (CAST(DatePart(month, [@field:Date_of_Birth]) as nvarchar) 
         + '/' + 
         CAST(DatePart(day, [@field:Date_of_Birth]) as nvarchar) 
         + '/' + CAST(DatePart(year, [@field:START]) as nvarchar)), 
 101)

This formula concatenates the month and day from the date of birth and the year from the start of the activity date.

As a result, we receive the concatenated string. So, we need to convert it to date. 

• BD in the year of EndDay

CONVERT(date, 
        (CAST(DatePart(month, [@field:Date_of_Birth]) as nvarchar) 
         + '/' + 
         CAST(DatePart(day, [@field:Date_of_Birth]) as nvarchar) 
         + '/' + CAST(DatePart(year, [@field:Enddate]) as nvarchar)), 
 101)

The same logic is applied to calculate the date of birth in the year of the end of the activity date.

This is a screenshot with the results example for visualization:

2) Then it is possible to combine this into one formula:
 

CASE 
WHEN
 
CONVERT(date, (CAST(DatePart(month, [@field:Date_of_Birth]) as nvarchar) + '/' + CAST(DatePart(day, [@field:Date_of_Birth]) as nvarchar) + '/' + CAST(DatePart(year, [@field:START])  as nvarchar)), 101) BETWEEN [@field:START] AND [@field:Enddate]

OR 

CONVERT(date, (CAST(DatePart(month, [@field:Date_of_Birth]) as nvarchar) + '/' + CAST(DatePart(day, [@field:Date_of_Birth]) as nvarchar) + '/' + CAST(DatePart(year, [@field:Enddate])  as nvarchar)), 101) BETWEEN [@field:START] AND [@field:Enddate]

THEN 'Birthday'

ELSE NULL

END

The logic of this formula is to check whether the BD in the year of StartDay or BD in the year of EndDay is between the StartDay and EndDate.

Hope this helps.

[bookish]

@CoopperBackpack The formula worked! Thank you so much! I only edited this to THEN [Field:Date_of_Birth] 

THEN 'Birthday'