- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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')
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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')
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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, 
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Sep 24, 2024 08:04 AM
@TheTimeSavingCo 2230 of the previous day
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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?