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.

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’] )

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.

I have the same issue

What do you need help with?

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.

And here is the resulting calendar view.

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.

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.

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()), “ DUE​”,“ :running_man: Waiting​:running_man:”))

This lets me see if today is their Birthday.

Still testing but all seems to be working very well.

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.

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!

The $60,000 question here is: How is the value you’re feeding into the formula formatted? That is, when you do MID({Something},1,2), what exactly is {Something}?

‘Concatenate’ is simply Computer Science-speak for ‘stick together.’ If you end up with the wrong things stuck together, it’s a problem with the things being stuck, not the process of sticking them.

@Lance_Recker’s original formula was written with the assumption the {Hire Date} field was in the specific format of 'MM/DD/YYYY". (In fact, it took me a while to understand why his formula wasn’t wrong; at first, I was positive the second MID() function should be MID({Hire Date},4,2).) If you’re using his MID() offsets and counts on an input other than MM/DD/YYYY, that’s your problem. You need to change your MID() calls appropriately.

Hope this helps!

That makes sense. I’m used to Excel, which sees a date as an integer (irrespective of how its display is formatted.) Your post helps me understand better how Airtable uses CONCATENATE. However, my input column IS in MM/DD/YYYY format, so I’m still stumped.

Could you post your current formula and a sample of the input data?

I’m new to Airtable and was just researching this problem. You all may have come to a solution to this a long time ago however, I thought I would add my solution which allows the field to remain in date format and be added to the calendars, This is a three-step process.

1. Create a field to calculate the employee’s AGE.
   
   DATETIME_DIFF(TODAY(),{Birthdate},‘years’)


2. Create a field to calculate a tentative Birthday (note: for some reason both the day and year were short by one so I had to add one to the year in this step and then in the next step I added 1 to the day). DATEADD({Birthdate},{Age}+1,‘years’)


3. Create a field to calculate the correct Birthday by adding a 1 to the day.
   
   DATEADD({BDate},1,‘day’)

I have found that this will allow the field to remain in the date format and thus be reflected on the calendar for the current year. I just hide any fields that users don’t need to see.

I am not a programmer, so someone may find a way to combine these steps into one.

Um, this post is a bit of a mess.

For anyone who stumbles across it while looking for a fix to a problem they are currently experiencing, please note:

The problem reported by the original poster and addressed in such detail over the following 16 replies is no longer an issue in Airtable.

Since June 2017, Airtable has been able to use calculated date fields — that is, formula fields whose result is a date — in calendar views. If you are currently having problems that seem similar, open a new Support ticket, and the know-it-alls who hang around this board will do their best to help.

FWIW, for those simply looking for an easy way to map a birthdate to its anniversary during the current year, try

DATETIME_PARSE(

    DATETIME_FORMAT(

        BirthDate,

        'MMMM D'

        )&

    ' '&

    YEAR(

        TODAY()

        ),

    'MMMM D YYYY'

    )

That converts {BirthDate} into a string of the format 'Month Day', concatenates it with the current year, and then coverts the concatenated string into an Airtable date with the value of this year’s anniversary of {BirthDate}.

@Byron_Miles

Typically, when one stumbles across a nagging, one-day (or, usually, of somewhat less than one day) discrepancy regarding some sort of DATETIME...() function, the usual culprit is a mismatch among date fields as whether or not to use GMT. Even if you’ve never explicitly set GMT, one or more of your date fields may have it toggled on. To correct, right-click on each date field, select ‘formatting’, and make certain your date fields are consistent about whether or not to use GMT. (It doesn’t matter which you select as long as all fields are set the same way.) (To see the current GMT settings, you may first need to toggle ‘Use a time field’ on. Once you have checked or set the GMT option, you can toggle off the time field if so desired.)

There have also been reports that date fields may sometimes not accurately report their GMT settings. While I cannot attest to that myself, if all of your fields appear to be set correctly, but you are still losing an amount of time equivalent to the difference between GMT and your local timezone, it’s probably worth stepping through your dates again and selecting and then deselecting (or deselecting and then selecting again) GMT.

Thanks, @W_Vann_Hall—as stated, Airtable now supports calculated fields in calendar views.

Since it seems like this topic being open might cause confusion, I’m going to go ahead and lock this thread.