Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Sep 13, 2024 10:35 AM
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).
Solved! Go to Solution.
Sep 25, 2024 07:43 PM
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')
)
Sep 14, 2024 02:01 AM
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')
)
Sep 16, 2024 05:37 AM
@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,
Sep 16, 2024 05:46 AM
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!
Sep 16, 2024 06:06 AM
@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
Sep 16, 2024 07:24 PM
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?
Sep 20, 2024 01:20 PM
@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)
Sep 20, 2024 10:13 PM
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?
Sep 24, 2024 08:04 AM
@TheTimeSavingCo 2230 of the previous day
Sep 24, 2024 07:57 PM - edited Sep 24, 2024 07:58 PM
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?