# Calculated field on birthdays

## Recommended Posts

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

##### Share on other sites

• 2 weeks later...

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

##### Share on other sites

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

##### Share on other sites

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

##### Share on other sites

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.

##### Share on other sites

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

## Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible. ×   Pasted as rich text.   Paste as plain text instead

Only 75 emoji are allowed.