Filter a date field based on day and month only

[alisonf34704358]

I have a custom Birthday date field on my users' records. I want to make a report that shows whose birthdays are coming up in the next few weeks. The problem is that I want to be able to see which dates are coming up regardless of the year entered in the Birthday field (e.g. if someone's birthday is listed as 11/1/1980, I still want them to show as an upcoming birthday even though the date has technically passed). Is there a way to filter like this using text mode, or do I have to manually fake everyone's birth year each year?

[cverges-medallia]

One idea would be to create a calculated field that is just month/day.  If you use MM-YY (with zero padding) as a format, then it would always be sorted in proper numerical order.

[alisonf34704358]

After a quick search I think I understand how to convert the Birthday field to a field like that, but how would I then compare it to today's date to see if it's within,say, 14 days?

[cverges-medallia]

Great question.  No clue. 

You could maybe do a Fusion process to calculate all the records that are coming up (because Fusion can calculate the date ranges involved) and then tag the records to flag them.  You'd also need to untag the records once they drop off.  It's a fairly straightforward set of scenarios, but you'd have to manage the scale of such a thing.

[skyehansen]

I'm wondering if faking it is good enough. Can you put a year on the company object that everyone is in (hopefully you only have the one company) and then create a calculated field that is formatted as a date, and looks at the month and day of the birthday field, adding it to the year of the company field, and then just update the year and recalculate the calculated field once a year? This way you get to keep it as a date.

As an aside, I wouldn't feel great if my company was storing my actual date of birth on my Workfront user record. This is considered PII (personally identifiable information).

[alisonf34704358]

To your last point, I don't actually have the years of birth stored, just month and day. I have the years set as 2023 right now. Didn't feel like explaining that in my original post. But I'm thinking now I'm just going to manually change the year like you said. It's only ~40 people.

[NicholeVargas]

If you have all the birthday years set as 2023 right now, you could run a user report and add the filter for User Birthday > Between > $$TODAY and $$TODAY+3w for example, and that will pull in all the birthdays in the next 3 weeks. You can change the date range to 1 month or 2 weeks, it would just be a slight variation in your date based wildcard. 

[alisonf34704358]

Yes, that is what I was planning to do. My question was more to do with next year and the years after--looks like I will have to keep updating their birth years every year.

[NicholeVargas]

You can create two different calculated custom fields, one for month, and one for day of month, to get numerical values, and then create a report that pulls in specific numbers or specific ranges.  

Calculated Custom Field Name = User Birthday Month
Format = Number
Calculation = MONTH({DE:Birthday})

Calculated Custom Field Name = User Birthday Day
Format = Number
Calculation = DAYOFMONTH({DE:Birthday})

For example, if you created these two calculated custom fields on your user custom form and recalculated the expressions in the background so the data gets filled in, you could leverage those in a user report. For example, if you wanted to see users who had birthdays in October and December, this is what your filter could look like: 

User Birthday Month > Between > 10 and 12