Help

Date Field Max Value

Topic Labels: Formulas
Solved
Jump to Solution
220 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!

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!!