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
Update date and time based and on another date and time but no later than 10:30PM PST
+18Best answer by TheTimeSavingCo
@TheTimeSavingCo column v2 does look correct. Thank you!
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')
)
+31Does 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')
)+18Does 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')
)@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, 
+31@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, 
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!
+18Interesting! 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
+31@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?
+18Ah, 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)
+31@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?
+18Yeap, 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
+31@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?
+18Ok! 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?
@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.
+31@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.
Hmm, does this look right? 
+31@TheTimeSavingCo column v2 does look correct. Thank you!
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')
)+18Great! 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')
)@TheTimeSavingCo thanks so much this worked as expected!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.