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?
Sep 21, 2017 07:32 PM
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!
Sep 22, 2017 06:32 AM
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.
Sep 22, 2017 08:49 AM
Could you post your current formula and a sample of the input data?
Mar 30, 2018 02:43 PM
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.
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.
Apr 03, 2018 05:52 PM
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.
Apr 03, 2018 06:01 PM
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.
Jan 15, 2024 02:43 PM
Sep 04, 2024 03:51 PM
Used the above example and added formulas so that it would automatically change the YEAR to the following year for dates that have passed. This way you don't have to reset the year every January. Works great in Calendar and there is a great Grid view for this as well: