Airtable formulas to generate 'pretty date' formats for collections + dynamic pages
Hi there! If you ever ask me about using collections in Duda to create dynamic pages or collection filter pages, I'll always HIGHLY recommend using Airtable because of how many AWESOME things you can do with it! This post will cover how you can use the formula field type to super power your collections and make it easier for your clients to fill in information, but still render their content beautifully on the site. Please note: you'll need to have an Agency plan or higher to use external collections.
By using one of these formulas, the website customer would never have to worry about typing the month correctly or accidentally using a comma in the wrong place. They'd just use the date picker column to add date information to the spreadsheet. So maybe they are publishing podcast episodes, blog posts, a new car listing, etc. -- anything where they want to display the date can be simplified with a quick formula. (And then you can use the date with another formula to officially show the episode when the date is valid.) This quick guide to pretty dates is part of a series on Airtable tips that Widget Pro is working on! :) LMK in the comments if you want to be tagged when I post more!
Video overview:
How to use the formula field
To add a formula in Airtable, click the + button to add a new column and select "formula" type. You'll need to add the formula right away to have it sync as a new column. You can reference other columns by putting their names in brackets {example}. Troubleshooting tip: Sometimes Airtable adds the second bracket for you automatically. If your formula won't save, check for a double bracket.
⛔️{example}} ⬅️example of a double bracket ⛔️don't keep double brackets.
✅{example} ⬅️example of a single bracket ✅
To try your first formulas, create a blank Airtable sheet. Add a column that is a Date type and name it Date. You can change it later, but by using the name Date, the formulas work without having to edit them.
Next, add a new column as a Formula type of field. Name this one something relevant for your collection. i.e. "Publish date formula". Paste in one of the following formulas:
Example formulas
Print as "January 1, 2022":
- DATETIME_FORMAT({Date}, "MMMM D, YYYY")
Print as "Jan 1, 2022":
- DATETIME_FORMAT({Date}, "MMM D, YYYY")
Print as "Wednesday, January 5, 2022":
- DATETIME_FORMAT({Date}, "dddd, MMMM D, YYYY")
Print as "01 January 2022":
- DATETIME_FORMAT({Date}, "DD MMM YYYY")
BONUS! This uses the 'CONCATENATE' formula. Print as "Wednesday the 5th":
- CONCATENATE(DATETIME_FORMAT({Date}, "dddd")," the ",DATETIME_FORMAT({Date}, "Do"))
EXTRA BONUS! This one uses an if statement to conditionally put the year in parentheses if it's from a previous year. Prints as "on Dec 17 (2021)" for a prior year and as "on Jan 2" for the current year.
- CONCATENATE("on ", IF(DATETIME_FORMAT({Date}, "YYYY")!=2022, DATETIME_FORMAT({Date},"MMM D (YYYY)"), DATETIME_FORMAT({Date},"MMM D")))
Let me know what formulas you try out below to make pretty dates, or drop your questions if you need some help/debugging of a formula you're trying our to streamline data management for your Duda collections. (If it's not for a Duda collection though you can also post in the Airtable community.) Here's a link to the formatting specifiers.
And I plan to write way more of these posts both on my site and in the community, so LMK what kinds of formulas would make your life easier and post below if you want to be tagged for future posts like this one! Up next will be how you can use a SWITCH statement to render things like the right author image or the right icon depending on the type of input. (As in your user can specify the author name or the asset type and the formula would take care of the rest for consistency/ accuracy/less manual work!) That's the Widget Pro way! :)
Comments
-
Release Date/Time option
I set up a date/time field for "release" (which Duda won't filter on) and then a formula field "released" that contains IS_BEFORE(release,now()) which returns 0 if we aren't yet at the specified release time, or 1 if after. Duda also won't filter on a number so I set the Duda collection to treat it as plain text, and look for 1.
Using that filter, my list widget renders rows that are past the release time, and the others are not shown.
Works as desired when I manually refresh the table.
Unfortunately, Airtable doesn't update "now()" automatically - the table needs to be refreshed to trigger that, it seems. I haven't figured out a way to do that automatically from within Airtable.
The automatic ~hourly refresh that Duda triggers on external collections will cause the "now()" and the formula containing it to be updated. though. 😊
1 -
Hey Bill! Just posting the same follow up from our FB message. :) If you use the DATETIME_DIFF() formula with NOW() it'll get you the up to date version consistently!
0
