Specific Solutions
Latest/earliest date across several fields
Inspiration
Sometimes you want to know the latest date across several fields.
While there are many ways to do this, I present a way that
Latest Date
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"
)
)
Earliest Date
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"
)
)
How it works
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!
Publish date: 2024-09-10
Want to print your doc?
This is not the way.
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.