Update date and time based and on another date and time but no later than 10:30PM PST

[airballer86]

Hi, I'm trying to update one date and time field {end date} based and on another date and time field {start date}. The catch is the {end date} cannot be later than 10:30PM PST (2024-09-01T10:29:59-08:00).

[airballer86]

@TheTimeSavingCo I think I confused myself lol. the time in the {end date} field will be 10 hours later than the time in the {start date} field BUT cannot be later than 2230. I think that is what you're saying above. 

[TheTimeSavingCo]

Hmm, does this look right? 

[airballer86]

@TheTimeSavingCo column v2 does look correct. Thank you!

[TheTimeSavingCo · Accepted solution]

Great!  Sorry, I forgot to include the formula:

IF(
  OR(
    DATETIME_FORMAT(
      {Actual End in PDT},
      'HH'
    ) > 22,
    AND(
      DATETIME_FORMAT(
        {Actual End in PDT},
        'HH'
      ) = 22,
      DATETIME_FORMAT(
        {Actual End in PDT},
        'mm'
      ) > 30
    ),
    (DATETIME_FORMAT(
      {Actual End in PDT},
      'DD'
    ) + 0) - 
        (DATETIME_FORMAT(
      {Start},
      'DD'
    ) + 0) != 0
  ),
  DATEADD(
    DATETIME_PARSE(
      DATETIME_FORMAT(
        Start,
        'DD MM YYYY'
      ),
      "DD MM YYYY"
    ),
    22.5 + 7,
    'hours'
  ),
  DATEADD(Start, {Hours to add}, 'hours')
)

[airballer86]

@TheTimeSavingCo thanks so much this worked as expected!