Calendar view of birthdays

[Tim_Wilson]

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?

[W_Vann_Hall]

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!

[David_Temple]

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.

[W_Vann_Hall]

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

[Byron_Miles]

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.

[W_Vann_Hall]

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.

[Katherine_Duh]

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.

[graiz]

I was able to get this to work. I used a slightly diffferent formula. 

IF(

{Birthday},

DATETIME_PARSE(

MONTH({Birthday}) & "/" & DAY({Birthday}) & "/" & YEAR(TODAY()),

'MM/DD/YYYY'

)

)

This is very simple and grabs the month, day and current year. The IF statement prevents blanks from showing up as January 1st.  The DATEPARSE is needed to allow the calendar widget and other views to give you date options. 

[dtinhb]

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:

IF(

    {Date of Birth},

    IF(

        MONTH({Date of Birth}) < MONTH(TODAY()),

        DATETIME_PARSE(

            MONTH({Date of Birth}) & "/" & DAY({Date of Birth}) & "/" & (YEAR(TODAY()) + 1),

            'MM/DD/YYYY'

        ),

        IF(

            AND(

                MONTH({Date of Birth}) = MONTH(TODAY()),

                DAY({Date of Birth}) <= DAY(TODAY())

            ),

            DATETIME_PARSE(

                MONTH({Date of Birth}) & "/" & DAY({Date of Birth}) & "/" & (YEAR(TODAY()) + 1),

                'MM/DD/YYYY'

            ),

            DATETIME_PARSE(

                MONTH({Date of Birth}) & "/" & DAY({Date of Birth}) & "/" & YEAR(TODAY()),

                'MM/DD/YYYY'

            )

        )

    ),

    BLANK()

)