Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Date Field Max Value

Topic Labels: Formulas
Solved
Jump to Solution
1257 4
cancel
Showing results for 
Search instead for 
Did you mean: 
KGB
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi!

Two formula questions - 

I have (4) different date fields with timestamps and need to identify the latest date across the four fields. When doing a Max value, it does not bring in the timestamp or makes it 12:00a as a standard. 

I also have (2) date fields that I need to calculate the datediff in hours and leaving the field blank if the second date field is empty instead of the NaN.

Please help! Thank you 🙂

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try these:

DATETIME_PARSE(
  MAX(
    IF(
      {Date 1},
      VALUE(
        DATETIME_FORMAT(
          {Date 1},
          'x'
        )
      ),
      0
    ),
    IF(
      {Date 2},
      VALUE(
        DATETIME_FORMAT(
          {Date 2},
          'x'
        )
      ),
      0
    ),
    IF(
      {Date 3},
      VALUE(
        DATETIME_FORMAT(
          {Date 3},
          'x'
        )
      ),
      0
    ),
    IF(
      {Date 4},
      VALUE(
        DATETIME_FORMAT(
          {Date 4},
          'x'
        )
      ),
      0
    )
  ),
  'x'
)
IF(
  AND(
    {Date 1},
    {Date 2}
  ),
  DATETIME_DIFF(
    {Date 2},
    {Date 1},
    'hours'
  )
)

Screenshot 2024-07-09 at 11.17.49 AM.png

Link to base

See Solution in Thread

4 Replies 4
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try these:

DATETIME_PARSE(
  MAX(
    IF(
      {Date 1},
      VALUE(
        DATETIME_FORMAT(
          {Date 1},
          'x'
        )
      ),
      0
    ),
    IF(
      {Date 2},
      VALUE(
        DATETIME_FORMAT(
          {Date 2},
          'x'
        )
      ),
      0
    ),
    IF(
      {Date 3},
      VALUE(
        DATETIME_FORMAT(
          {Date 3},
          'x'
        )
      ),
      0
    ),
    IF(
      {Date 4},
      VALUE(
        DATETIME_FORMAT(
          {Date 4},
          'x'
        )
      ),
      0
    )
  ),
  'x'
)
IF(
  AND(
    {Date 1},
    {Date 2}
  ),
  DATETIME_DIFF(
    {Date 2},
    {Date 1},
    'hours'
  )
)

Screenshot 2024-07-09 at 11.17.49 AM.png

Link to base

KGB
5 - Automation Enthusiast
5 - Automation Enthusiast

I got the second one to work! The first is still not returning the timestamp

What issue are you facing with it?  If you could provide a read-only invite link to a duplicated copy of your base with some example data I could take a look at this for you!

KGB
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi! I am so sorry, was on vacation... which may have helped because I realized it wasnt working since I had time off in the formatting 🙂 Thank you SO much!!