Skip to main content

Extract middle date from a lookup of up to 3 dates

  • January 12, 2024
  • 3 replies
  • 43 views
D
Forum|alt.badge.img+7

Hi folks,

Can anyone help my failing Friday brain out please? I have a lookup field pulling through a maximum of 3 payment dates from a payments table. I need to break those dates down across 3 columns; earliest date, middle date and latest date. I have a MIN and MAX in place displaying the earliest and latest values, it's the middle date value I cannot figure out. I don't suppose anyone can solve this with a formula can they? I'm hoping to keep the workarounds to a minimum if possible.

Much virtual thanks in advance!

3 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • January 12, 2024

Use a series of back-and-forth rollups. You have rollups in your main table identifying the max and min dates. Bring those max and min dates to the payments table with rollups of those rollups. Then in the payments table have a formula field that compares the payment date to the max and min. If the payment date is neither the max or min, it must be the middle, so have the formula output that it is the middle payment. Finally, use another conditional rollup to bring the middle payment into the main table using a condition based on that formula field. 

Not that this system will only work if all three dates are different.

It can be a lot of rollup fields in both tables, but with only three linked records per main record, the calculations should happen at a reasonable speed. 

Alexey_Gusev
Forum|alt.badge.img+25

Hi,
I have a feeling that 'series of back-and-forth rollups' solution is more 'correct', but it might be harder to implement.
On the other hand, you can substitute min and max from dates field and format the rest up to your needs
You should also format according to your time zone. See links to change the format and time zone.


Formula to copy-paste. Adjust field names and your time zone.

SET_TIMEZONE(DATETIME_FORMAT( SUBSTITUTE(SUBSTITUTE(''&{dates},''&{min},''),''&{max},''), 'YYYY-MM-DD HH:mm'),'Europe/Kiev')




S
Forum|alt.badge.img+21
  • Sho
  • Inspiring
  • January 15, 2024

Hi @Data_things,

If the record in the Lookup destination has a field that displays the X value of the date, the number of extra fields can be reduced.

IF({Date}, VALUE(DATETIME_FORMAT({Date},"X")) )

Refer to this formula in the Rollup field.

DATETIME_PARSE( SUBSTITUTE(SUBSTITUTE(values&"", MAX(values)&"", ""), MIN(values)&"", "") , "X" )

This formula will result in an error if there are the same dates or if there are more than 4 dates.
You can work around this by setting the INDEX option of SUBSTITUTE to 1, whichever you want to show the min or max, even if there are the same dates.

 

Terms & ConditionsAccessibility statement