Calendar Date (or Julian Date) NO YEAR

[Elizabeth_Crisf]

I would like to mark the time of year that something happens in the natural world so that it would appear on a calendar, every year. In Google Calendar, this is just a date field that is marked as “every year” but it could also just be a date field with no year.

Another thing that would be handy for scientific applications would be the availability of the Julian Date as a date field. (I guess I can do that as a simple formula calculation though.)

Thanks,

[Justin_Barrett]

The calendar view in Airtable isn’t really meant to be a super-robust calendar. Because the events it displays are tied to individual table fields, it’s going to be somewhat limited. To achieve what you want, there are two options I can think of.

One is similar to something that I do on my task list. I have what I call “scheduled” tasks that have fields where I can choose the frequency of the task:

I then use a formula to calculate when the next iteration of that task should be (using DATEADD), and use Integromat to update the due date automatically when any of these scheduled tasks are complete. In short, the task isn’t deleted after it’s complete, but gets “recycled” by having its due date automatically updated for the next time it’s needed.

You could set up something similar so that these yearly events are moved after they have passed. You would set up your table so that Integromat could easily find the recurring events using a daily search. If Integromat finds that a given event’s date has passed, it would add a year to that date. For example, say there’s an event on May 27, 2019. Once May 28th comes along, the Integromat scenario would change that event date to May 27, 2020. It would obviously disappear from your view of May 2019, and if that’s a problem, this might not be a viable solution.

If you want all dates—past and future—to always appear, you could do something similar, but have Integromat add a new record instead of editing an existing one.

[Elizabeth_Crisf]

Thanks for explaining that. I was looking for a database solution for phenology tracking. As with so many of these cool tools - the primary users are for workflow and business management (which I actually also need, so it’s all very helpful), but it leaves us science-types with a lot of working-around.

I’m almost looking at having a separate table for each species, with records for first bloom, first leaf, etc and fields for years? And then I have to figure out how to graph multiple species’ date of first bloom (which could be julian date) over multiple years. Is the “block” really the only way to graph things? It doesn’t offer very much customization of the data presentation. I’ve been using Tableau for data visualization - it would take airtable awhile to compete with that, but they should try! I’d be a fast convert.

[W_Vann_Hall]

I agree that v 1.0 of the Chart Block can be, um, challenging. From your description, I don’t think the approaches would be complementary, but you might want to see if any of the workarounds I use in my SF Monthly Rainfall base on Airtable Universe could be of use. There are some nice Airtable-to-[visualization tool] integrations out there — but, alas, so far none are mine, so someone else needs to chime in, here.

Also, you’ve probably discovered this, but DATETIME_FORMAT() and DATETIME_PARSE() support a raft of format specifiers, including some that provide easy access to Epoch date. That’s only a 70-year timespan, though, which is likely too narrow for SSI’s purposes, so you’re probably stuck with rolling your own Julian conversion…

[Elizabeth_Crisf]

Thanks for those links… I do appreciate your SF Monthly Rainfall implementation. As a former hydrologist, that’s a handy example for me to use to figure out the implementation of temporal data here.

Elizabeth

[Brooklyn_Music_]

Hi Justin! I think this might be a great solution for what I’m trying to do with our music school. We’re displaying an Airtable calendar to show available openings in classes. Ideally, the “date/time” field would automatically update each week, so that you’re always looking at the current week when you go to view that calendar.

Could you expand on how you got this integration going with Integromat? I’ve got a DATEADD formula displaying the next iteration (one week later), but not sure how this all ties in with Integromat.

Thank you!

[Brooklyn_Music_]

Got it. I have a “Day/Time” field, which drives the calendar, and a “Next Week’s Day/Time” field, which uses DATEADD. I then added a column with a function that returns true if “Day/Time” is earlier than today:

IF{Day/Time} < Today(), “true”)

Integromat scans the view in Airtable and returns only those records where the above testing column is not empty. It then updates those records by replacing the “Day/Time” field with “Next Week’s Day/Time”. This will run every day and everything updates automatically. Phew!

[Justin_Barrett]

Glad that you got it working! Sorry that I couldn’t respond sooner. My work schedule doesn’t allow me to spend nearly as much time in here as I used to.