Help

Find the maximum of three date fields including time stamp

Topic Labels: Formulas
Solved
Jump to Solution
83 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Koen360
4 - Data Explorer
4 - Data Explorer

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'
   )
)

 

1 Solution

Accepted Solutions
Koen360
4 - Data Explorer
4 - Data Explorer

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'
   )
)

 

See Solution in Thread

1 Reply 1
Koen360
4 - Data Explorer
4 - Data Explorer

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'
   )
)