Extract latest date from multiple date columns
Hi @Pascal_Krebs - the “best” way to do this would be to have the products and the service dates in separate tables (multiple service dates linked to a single product). With this set up you could do a roll-up on the service date and get the max/most recent value.
However, if you don’t want to do a two-table solution, you can still get there with multiple date columns, just needs a bit of wrangling. One solution is this:
The Date Diff columns are essentially the same - example:
IF({Date 1}, DATETIME_DIFF(TODAY(), {Date 1}, 'days'), 99999)
This evaluates:
If Date 1 exists
Get the number of days between Date 1 and today
otherwise 99999
The Min Date Diff column gets the min of the 3 date diff columns:
MIN({Date 1 Diff}, {Date 2 Diff}, {Date 3 Diff})
Having got the min date diff (i.e. the most recent), you can translate this back to the most recent date using:
IF({Min Date Diff} < 99999, DATEADD(TODAY(), -1 * {Min Date Diff}, 'days'))
As you can see 99999 is a dummy value that won’t occur in real life - just allows you to do the MIN function when a column has no date.
Ideally, you’d be able to do:
MIN(DATE 1, DATE 2, DATE 3)
But MIN() only works on integers, not dates, so you need to convert to a number before doing the MIN evaluation.
Obviously, hide the columns you don’t want to see.
JB
Hi Jonathan,
This is amazing as it solves part of my problem. I was just wondering if it would be possible to help me on on the following:
So basically, the situation is that I have date columns but with each date, I have to put a score with it. So basically it looks like this (as attached). I am wanting to get the latest score as well. I have sorted the date out but also want to get the score associated with that date. Is this going to be possible? I have only shown Jan and Feb but it will contain many more months if it can work out.
Thank you in advance.
