Extract latest date from multiple date columns

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

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

1 Like