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

[TheTimeSavingCo]

Does this look right?  I'm not sure how to handle daylight savings here though

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
    )
  ),
  DATEADD(
  DATETIME_PARSE(
    DATETIME_FORMAT(
      Start,
      'DD MM YYYY'
    ),
    "DD MM YYYY"
  ),
  22.5 + 7,
  'hours'
),
DATEADD(Start, {Hours to add}, 'hours')
)

Link to base

[airballer86]

@TheTimeSavingCo Thank you for the formula. The formula works, however it doesn't keep the end time at 10:30 PM or earlier. Is there a way to add that constraint? 

thanks, 

[TheTimeSavingCo]

Interesting!  Could you provide a screenshot of it not working?  The screenshot in your post doesn't show any end times past 10:30pm, so I don't know how to replicate the issue you're facing!

[airballer86]

@TheTimeSavingCo the first campaign has an end time of 3:00 AM the following day. It should have the max time of 10:30pm the same day. If I’m understanding your liogic correctly 

[TheTimeSavingCo]

Ah, I see!  I made it handle up to 2359 of the same day, and so if the end time is the next day it just displays that value instead.  Hmm, to handle this we'd need a latest time that we'd want it to display 2230 for.  For example, if the end time is the next day at 0700, would we show 0700 of the next day or 2230 of the previous day? 

[airballer86]

@TheTimeSavingCo Sorry for the delayed response! And yes, I need it the latest time to be 10:30PM PST 10:30PM PST (T10:29:59-08:00)

[TheTimeSavingCo]

Yeap, I understand!  If the end time is the next day at 0700, would we show 0700 of the next day or 2230 of the previous day?

[airballer86]

@TheTimeSavingCo  2230 of the previous day

[TheTimeSavingCo]

Ok!  So there's no cutoff timing, as long as it's past 2230 of the start date it just shows 2230 of the start date, E.g. if you add 72 hours and the end date is 3 days later, you still want it to be 2230 of the start date?