Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 16, 2016 08:26 PM
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?
Aug 19, 2016 09:02 PM
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.
Aug 19, 2016 09:33 PM
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.
Aug 19, 2016 10:32 PM
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.
Aug 20, 2016 07:02 AM
I agree. Adding more entries could be an issue. I’ll have to ponder this further.
Aug 26, 2016 07:44 AM
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.
Aug 26, 2016 04:44 PM
@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.
Aug 26, 2016 04:57 PM
Yes. It would automatically switch years.
Jan 02, 2017 08:31 PM
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.
Jul 12, 2017 11:28 AM
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:
Sep 20, 2017 10:29 AM
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!