Kuovonne's Guide to Airtable
Share
Explore
Kuovonne's Guide to Airtable
Specific Formulas

Earliest / latest date across columns

⁠
⁠
Tip Jar
⁠
Thank Kuovonne for creating this content!
⁠
⁠
⁠
⁠
⁠

Inspiration

Sometimes you want to know the latest date across several fields.
This is slightly different from finding the earliest/latest date in a linked record.

Discussion

Sometimes you want to know the latest date across several fields. ​
image
⁠
⁠
While there are many ways to do this, I present one way that
works with date/time fields as well as date fields
can be easily expanded to include additional date fields
preserves the original time, down to the millisecond
is blank when all the input date fields are blank
⁠
Here is a formula to get the latest date.
IF(
OR( {date1}, {date2}, {date3} ),
DATETIME_PARSE(
MAX(
IF(
{date1},
VALUE(DATETIME_FORMAT({date1}, "x"))
),
IF(
{date2},
VALUE(DATETIME_FORMAT({date2}, "x"))
),
IF(
{date3},
VALUE(DATETIME_FORMAT({date3}, "x"))
)
),
"x"
)
)

⁠
And here is one to get the earliest date.
IF(
OR( {date1}, {date2}, {date3} ),
DATETIME_PARSE(
MIN(
IF(
{date1},
VALUE(DATETIME_FORMAT({date1}, "x")),
999999999999999
),
IF(
{date2},
VALUE(DATETIME_FORMAT({date2}, "x")),
999999999999999
),
IF(
{date3},
VALUE(DATETIME_FORMAT({date3}, "x")),
999999999999999
)
),
"x"
)
)

⁠
These formulas work by first checking that at least one of the date fields has an input. Then the formula converts each date to its Unix millisecond timestamp. The formula then takes the min or max Unix timestamp and converts it back into a date. When looking for the minimum date, the formula uses a very large number for blank dates so that the proper minimum date will be picked.

⁠
⁠
⁠
Thank Kuovonne for creating this content!
⁠
Give Kuovonne a sandwich
⁠
Signup for Coda w/ referral link
⁠
⁠
⁠
Buy from my Gumroad Store
⁠
⁠
⁠
⁠
⁠
⁠
⁠
Gallery
Share
 
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.