Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Automations
Solved
Jump to Solution
605 14
cancel
Showing results for 
Search instead for 
Did you mean: 
airballer86
7 - App Architect
7 - App Architect

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

 

1 Solution

Accepted Solutions

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

See Solution in Thread

14 Replies 14

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

Screenshot 2024-09-14 at 5.00.23 PM.png

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

@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? 

email_nolaterthan.jpg

 

thanks, 

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!

@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 

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? 

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

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?

@TheTimeSavingCo  2230 of the previous day

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?