Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Find the maximum of three date fields including time stamp

Topic Labels: Formulas
Solved
Jump to Solution
563 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Koen360
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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
6 - Interface Innovator
6 - Interface Innovator

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