I have multiple columns with service-dates for on product. Our products have to be serviced by one of our mechanics after specified periods (1 year / 3 year cylces). So every product-row has multiple date columns- each date column representing the date of a service which was made. So the columns are service-date 1, service-date 2 - etc… To calculate the next mandatory service for a product I need to extract the latest service-date to a seperate column and then add the 1 or 3 years (depending on product) by formula. I’m just not sure how to extract the latest date - since the products don’t all have the same latest service column. A new product may have only one entry - so for ex. 01.01.2019 in service-date1 - an older product has had more services so maybe 01.01.2015 in service-date1 - and 01.05.2019 in service-date2 etc… but I need to find the latest service date of all the products and put them in a new column to calculate the next service date. Looking forward to hearing creative solutions.
Extract latest date from multiple date columns
+18Hi @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 @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.
Hello,
I created a formula which can be useful. It is a formula which select the latest date from multiple date column. I got inspired from JonathanBowen's explanation. Please find below the formula:
The formula counts how many days there are between the selected date and today. Then the date with the less value will be selected to be the latest date. The cells in blank will be replace by "1990-01-01". When I tried this formula with blank cell I got the "NaN" error.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.