For a given row I have like 8 date columns. Any of the 8 date columns could be the “latest date” and i’m trying to compare the latest date from that collection to today’s date. Any way to do this other than a crazy 64+ conditional IF formula on Airtable? lol. Like returning a max date in a given row then just calculating that against today’s date?
Comparing Dates Across A Row
+2
What fields are those? Sounds me to me like should be other approach to solve your needs, like a Milestones table or something like that, where you could easily get the latest.
+2What fields are those? Sounds me to me like should be other approach to solve your needs, like a Milestones table or something like that, where you could easily get the latest.
We’re a VC firm and these are dates of different interactions with startups. Unfortunately it doesn’t flow in a linear order. We have a date for a technical meeting, a date for a business meeting, a date for an internal meeting where our team discusses the startup, a date for selecting a startup to invest in, and a date for dropping that startup. These aren’t fully linear. I can’t combine the different columns into a single column drop-down style because each interaction type we want to record a date (because we want to run analytics on each of those specific interactions). Hopefully that makes sense!
We’re a VC firm and these are dates of different interactions with startups. Unfortunately it doesn’t flow in a linear order. We have a date for a technical meeting, a date for a business meeting, a date for an internal meeting where our team discusses the startup, a date for selecting a startup to invest in, and a date for dropping that startup. These aren’t fully linear. I can’t combine the different columns into a single column drop-down style because each interaction type we want to record a date (because we want to run analytics on each of those specific interactions). Hopefully that makes sense!
Are you following up in this?
The Milestones table that I suggested is a good solution.
+21I’ve come up with a solution that I think will work for you. Here’s how it looks with a bunch of formula fields hidden:
And here’s the full view:
For each date field, there’s an associated “Numeric” field that converts the date into a number. Here’s the formula for Date 1 Numeric as an example:
IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1},"YYYYMMDD")), 0)
The IF simply forces the value to be zero in case a date isn’t entered yet, so that the later formulas still operate correctly.
By formatting the date this way, you can compare all of the numeric values against each other and ensure that the largest one is the latest date, which is what the Max Numeric field does:
MAX({Date 1 Numeric}, {Date 2 Numeric}, {Date 3 Numeric})
The Max Date field then turns the number back into a date:
DATETIME_PARSE({Max numeric}, "YYYYMMDD")
BTW, the Max Numeric and Max Date formulas can be combined in a single field, but I kept them separate for the purpose of testing.
The final field then compares the max date against TODAY():
DATETIME_DIFF(TODAY(), {Max Date}, "days")
A negative value means that today is earlier than the maximum date. If you want that reversed, just swap TODAY() and {Max Date}.
+2I’ve come up with a solution that I think will work for you. Here’s how it looks with a bunch of formula fields hidden:
And here’s the full view:
For each date field, there’s an associated “Numeric” field that converts the date into a number. Here’s the formula for Date 1 Numeric as an example:
IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1},"YYYYMMDD")), 0)
The IF simply forces the value to be zero in case a date isn’t entered yet, so that the later formulas still operate correctly.
By formatting the date this way, you can compare all of the numeric values against each other and ensure that the largest one is the latest date, which is what the Max Numeric field does:
MAX({Date 1 Numeric}, {Date 2 Numeric}, {Date 3 Numeric})
The Max Date field then turns the number back into a date:
DATETIME_PARSE({Max numeric}, "YYYYMMDD")
BTW, the Max Numeric and Max Date formulas can be combined in a single field, but I kept them separate for the purpose of testing.
The final field then compares the max date against TODAY():
DATETIME_DIFF(TODAY(), {Max Date}, "days")
A negative value means that today is earlier than the maximum date. If you want that reversed, just swap TODAY() and {Max Date}.
WOOOOOOOW I’d give you a hug right now if it was real life :slightly_smiling_face: I’m going to go try this out tomorrow hahaha. THANK YOU so much!!! :heart_eyes:
As an aside to @Justin_Barrett’s excellent solution, above, you can also convert datetimes to a numeric value using DATETIME_FORMAT() with the ‘X’ format specifier. This returns what’s known as Epoch time: The number of seconds since midnight, January 1, 1970. (The ‘x’ specifier supposedly returns Epoch time in milliseconds, but as it appears internal Airtable timestamps have a one-second granularity, I’ve not found much use for it.) This can come in handy should you ever need to differentiate between two events happening the same day, and it can be used to calculate intervals that span the end of a year. You can use DATETIME_PARSE() with ‘X’ to convert back to a standard datetime type. (See the first item in this reply for more info.)
Login to the community
No account yet? Create an account
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.