Skip to main content
Solved

Find the maximum of three date fields including time stamp

  • June 10, 2024
  • 1 reply
  • 60 views
Forum|alt.badge.img+5

I found this formula in the community. However I want to include a timestamp aswell. Since {date 1}, {date 2}, {date 3} may occur on the same day. I tried adding a format specifier but it wont work... The formula sets the time to a default 12:00am instead of the actual time that is showing on the datefields.

IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDD'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDD'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDD')))
      ), 
      'YYYYMMDD'
   )
)

 

Best answer by Koen360

Well... that was easier then I thought. Here's the answer for future ppl who may struggle.
I used the wrong format specifier... I wanted to have a DD-MM-YYYY LT format. But that doesn't work with the MAX()-function I suppose. It worked by adding the letter 'x' in the datetime_parse. And it came out the way I wanted... Yeeey... Also don't forget to set the same GMT's on all the datefields

 

IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDDx'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDDx'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDDx')))
      ), 
      'YYYYMMDDx'
   )
)

 

View original
    This topic has been closed for replies.

    1 reply

    Forum|alt.badge.img+5
    • Koen360
    • Author
    • New Participant
    • Answer
    • June 10, 2024

    Well... that was easier then I thought. Here's the answer for future ppl who may struggle.
    I used the wrong format specifier... I wanted to have a DD-MM-YYYY LT format. But that doesn't work with the MAX()-function I suppose. It worked by adding the letter 'x' in the datetime_parse. And it came out the way I wanted... Yeeey... Also don't forget to set the same GMT's on all the datefields

     

    IF(
   OR({Date 1}, {Date 2}, {Date 3}), 
   DATETIME_PARSE(
      MAX(
         IF({Date 1}, VALUE(DATETIME_FORMAT({Date 1}, 'YYYYMMDDx'))), 
         IF({Date 2}, VALUE(DATETIME_FORMAT({Date 2}, 'YYYYMMDDx'))), 
         IF({Date 3}, VALUE(DATETIME_FORMAT({Date 3}, 'YYYYMMDDx')))
      ), 
      'YYYYMMDDx'
   )
)

     

    Terms & ConditionsAccessibility statement