Calendar View based on Lookup field dates [SOLVED]

[Christopher_Sm1]

I made this a feature request, as it appears from Help documentation that it cannot currently be done.
I have dates in one table which are in a lookup field (so Assets in a table of assets find out their deadline from the deadline of the project in which they’re included (Projects are in a separate table).

So I want to show the Assets in a Calendar view showing when they need to be done, but the date field for the calendar view isn’t a Date field—technically it’s a Lookup field—so it doesn’t show as an available date. If I copy the value from the Lookup field to a dummy Date field I run into the problem Y_K posted: that a Formula Field doesn’t work (either) for the purposes of the Calendar view.

Basically, Airtable users need to use any field formatted as Date as input for the Calendar View.

Thank you!! Airtable is miraculous. (And PLEASE don’t let anyone buy the company!)

[Matt_Bush]

Good news! Today we’re introducing support for formulaic date fields as the date field of a Calendar view. This includes all formulas and rollups where the output is a date, and some lookups (so long as the record’s lookup field only contains 1 linked date value).

Eligible formula, lookup, and rollup fields will now appear in the date field chooser when you configure a calendar view.

[Vernon_Fowler]

Yihaa! Thank you @Matt_Bush

[John_Beaudoin]

@Matt_Bush Will it ever be possible, do you think, to a have a field containing multiple dates represented in a calendar view? The use case is a planning tool that would assign multiple dates to a task or event. The basic effect would be sort of like a repeating task. Or is there another way to do this already?

[Matt_Bush]

If you have a lookup field containing multiple dates (Table A), then there’s another linked table (Table B) with a date field that’s the source of those dates. The best way to do what you’re describing is to create a calendar view on Table B, which contains the date field. If needed, you could add lookup fields to Table B to pull more information from Table A.

In the future we may make it possible to use a field containing multiple dates in a calendar view, but it’s not something we’re prioritizing at the moment.

[AlyB]

Hi Matt,
I’m glad to see you have added support for formulaic fields in Calendar View. My team is international and navigating time zone differences has been really tricky with AT. To work around this problem, all our event start dates are converted to US Eastern Time using a formula. I want to use this formula field in the Calendar View, but it does not appear in the date selector window. How do I make my formula field qualify for the Calendar View field picker??

DATETIME_FORMAT(SET_TIMEZONE({StartDateTime}, ‘America/New_York’), ‘ddd, M/D/YY h:mm A’)

[Matt_Bush]

To get your formula to appear as an option in the calendar view, remove the call to DATETIME_FORMAT, leaving:

SET_TIMEZONE({StartDateTime}, ‘America/New_York’)

The reason is that a formula field that uses DATETIME_FORMAT has a result type of text, not a date, so it isn’t compatible with calendar views. Instead of using DATETIME_FORMAT to get the appearance you want, use the Formatting tab of the Formula editor, which lets you choose among the same formatting options as you would find in a Date field.

[AlyB]

Matt_Bush:

SET_TIMEZONE({StartDateTime}, ‘America/New_York’

Bizarrely, when I remove “Datetime_Format ( )” and leave everything else the same, the time output is incorrect. My original formula with the Datetime call is still working correctly. Any idea what’s going on?

SET_TIMEZONE(StartDateTime,‘America/New_York’)

Example:
StartDateTime: 12:00 PM
formula without Datetime( 😞 10:00 AM - incorrect
formula with Datetime( 😞 6:00 AM - CORRECT!