Help

Calendar view of birthdays

Topic Labels: Views
14023 26
cancel
Showing results for 
Search instead for 
Did you mean: 
Tim_Wilson
7 - App Architect
7 - App Architect

I have a table with information about kids on a team I coach. I have a field for their birthdates and use that to calculate their age divisions. I’d like to use a calendar view to show upcoming birthdays, but the calendar view based on their birthdates doesn’t work, of course, because their birthdates aren’t in the current year. Is there a way to do this?

26 Replies 26
Lance_Recker
5 - Automation Enthusiast
5 - Automation Enthusiast

I would have to try this, but here’s an idea: what about extracting the month and day from the birthdate using a formula and then concatenating them back together with the current year to get their birthday?

I tried this in a base I have containing an employee hire date field. To create a column with their hire date anniversary day, I created a new field containing the formula below.

CONCATENATE(MID({Hire Date}, 1, 2), MID({Hire Date}, 3, 4), YEAR(TODAY()))

This formula basically takes select pieces from the hire date (the month + the day) and adds the current year based upon today and then joins them all together. Once you try this and everything looks ok, you’ll then have to convert the column from formula type to date type and create a calendar view based upon that date.

76b1e03aa99ca65267fe2b152f28b1dbfb9adf19.png

Mics_Sky
6 - Interface Innovator
6 - Interface Innovator

Another way:
You can try this and use the optional parameters if needed and make sure you are in US format ( didn’t try myself !).

DATETIME_PARSE ( “’”
& MONTH( {Hire Date} )
& “-”
& DAY( {Hire Date} )
&"-"
& YEAR(TODAY(), [ format ], [‘locale’] )

Tim_Wilson
7 - App Architect
7 - App Architect

Thanks for the replies. I tried the datetime_parse approach, but didn’t realize that you can coerce a formula into a date. I’ll play with this some more. Thanks again.

Emma_Dicks
4 - Data Explorer
4 - Data Explorer

I have the same issue

What do you need help with?

Tim_Wilson
7 - App Architect
7 - App Architect

The calendar views will only display Date fields. Unless it’s possible to take a formula field and force Airtable to treat it like a date, I haven’t found a way to make this work yet.

I’ve been able to do it repeatedly. Using the formula I provided above, I am able to produce the date I need. Then, after the formula has been applied, I can change the field type from formula to date type. Once done, it can then be used for a calendar view.

Here’s a quick GIF showing how I did it.
40177dec4c17e8a1afd750ce48f3395b9475a5da.gif

And here is the resulting calendar view.
Screen Shot 2016-08-19 at 10.17.08 PM.png

Tim_Wilson
7 - App Architect
7 - App Architect

I tried it again, and your solution using the CONCATENATE function works. I was unable to get it to work with the DATETIME_PARSE approach. Thanks for the help, Lance.

This is a very clever solution. But, what happens when you go to add another kid/record to the database? The “New Date” field will not populate automatically since the formula has now been removed.

You will need to go back and change the column to a formula format again adding the formula once more and repeat the above process it seems each time, correct? I suppose that if the roster of players is constant over a season that wouldn’t necessarily be too much of an issue.

But, it would be nice if a solution could be found that avoided this manual re-touching of the configuration of the table each time.

Mark, that’s a great point that I hadn’t considered. I do, in fact, add people to the database at various times during the year. It’s not too much of a hassle to “rinse and repeat” on this, but it would be nice to avoid it.

The ideal solution might be to have the calendar views optionally ignore the year in the Date field. It would also ensure that the birthdays show up properly when scrolling forward in the calendar to the next calendar year. The CONCATENATE approach fails to show any birthdays in any year other than the current one.

So perhaps this simply request for help has morphed into a feature request.

Tim, yes I think this is more complicated than first appears. It seems you would need to be able to embed somehow a formula into the date field format so that the date instead of being manually entered would be generated by the formula based off another field (the original birthday field). An option box to choose that?

And, you are so right that every January you would have to redo this again to get the 2017 dates to appear and so forth each year. So perhaps a feature could be added to make the date field have optional “recurring” date parameters, similar to other calendar applications?

Would love to hear what others including the Airtable staff think about this. Best of luck on your new season and hope you find a solution.

I agree. Adding more entries could be an issue. I’ll have to ponder this further.

Jeremy_Cady
4 - Data Explorer
4 - Data Explorer

I used the DATETIME_PARSE function to do this.

First, I created a date column “Birthdate” with their actual date of birth.

Second, I created a formula column “Birthday” and provided the following function:
DATETIME_PARSE((((MONTH(Birthdate) & “-”) & DAY(Birthdate)) & “-”) & YEAR(TODAY()))

This would not work with the calendar view, as it only uses actual date columns. However, the output of DATETIME_PARSE is a date, and a filter will recognize it as such.

So, I created a grid view called “Upcoming Birthdays” and used the “Birthday” column in my filter, and filtered by “within the next month”. You may filter by many other variables as well.

It would be great to have in calendar view, but this alternative will update automatically as I enter in new birthdates.

@Jeremy_Cady, this is a great addition to the topic and seems to work well. Question, since this is retained as a formula field (and recognized as a date), I assume that once you pass January 1 each year, the Birthday field will automatically update to the new current year, e.g. 2017 instead of 2016 this coming January. Correct?

Thanks again for the great formula.

Yes. It would automatically switch years.

Michael_Fields
5 - Automation Enthusiast
5 - Automation Enthusiast

Thanks for the start Jeremy_Cady. This gave me the direction for something I was trying to do. I wanted a way to visually show when today was a persons Birthday. I also plan to use this to show when equipment expires.

I already had a field named DOB. I created a field named AGE and added this formula, DATETIME_DIFF(TODAY(), DOB, ‘years’) this gives me the persons age.

Then I created a field named Birthday with this formula, DATETIME_PARSE((((MONTH(DOB) & “/”) & DAY(DOB)) & “/”) & YEAR(TODAY())) This shows their Birthday this year.

Finally I created a field named BDate that would show me when their Birthday was due and added this formula, IF(Birthday = TODAY(), “Done”,IF(IS_BEFORE(Birthday, TODAY()), “ :white_check_mark: DUE​:white_check_mark:”,“ :running_man: Waiting​:running_man:”))
This lets me see if today is their Birthday.

Still testing but all seems to be working very well.

Lisa_Altizer
6 - Interface Innovator
6 - Interface Innovator

Just curious if the Airtable developers could create an option for a short date in the Format drop-down list, would this allow the dates to appear in the calendar as recurring?

I would also love a way to send myself an email reminder of any upcoming birthdays a week in advance. :slightly_smiling_face:

David_Temple
5 - Automation Enthusiast
5 - Automation Enthusiast

Trying to convert a DOB to Birthday. Using the CONCATENATE formula above, my output is giving year of birth, dash, current year with a zero in front. e.g. 1964-02017 . I’m trying to find more instruction on the CONCATENATE command, but coming up blank. Any ideas? Thanks!