Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Automate END TIME based on START TIME, DURATION

Topic Labels: Automations
Solved
Jump to Solution
931 6
cancel
Showing results for 
Search instead for 
Did you mean: 

I’m following up on a previous solution ScottWorld provided for another user. I apologize if there’s a better way to link to the old closed post.

Anyway, the goal is to Automate the END TIME based on START TIME, and DURATION. The formula Scott suggested was:

DATEADD({Start Time}, {Duration}, ‘minutes’)

When I put this Formula into the END TIME formula field exactly as is, and the DURATION field empty, the END TIME is automatically set to 5 hrs after the START TIME. When I enter “3:00” (3 hrs) into the DURATION field, the END TIME is automatically set to 7 Days and 20 Hrs later.

Obviously, I’m misunderstanding something.

1 Solution

Accepted Solutions

Change the time zone settings for the {Start Time} and the formula field so that they match (local time vs. GMT).

See Solution in Thread

6 Replies 6

Test using seconds as the time unit. If you don’t want to calculate a date when Duration is empty, wrap your current formula in an IF statement.

IF({Duration}, DATEADD({Start Time}, {Duration}, 'seconds'))

I’m getting the same issue, but at least it’s consistent. The END TIME is always 8 hours later than I expect.

If the START TIME is 11am, and the DURATION is set to 3 hrs, END TIME is 10pm.
If the START TIME is 11am, and the DURATION is set to 61 seconds, END TIME is 7:01pm.

note: if I enter the duration directly into the formula, as opposed to referencing a DURATION field, I get the same exact issue.

Change the time zone settings for the {Start Time} and the formula field so that they match (local time vs. GMT).

Thank you. I’m not exactly sure what you mean. How do I change the Time Zone settings?

In the meantime, I set the formula to:
DATEADD({Start Time}, {Duration}-8, ‘hours’)
which gives me the results I want, but I wonder if this workaround might cause some issues down the road.

Whew. I was making it way harder than it had to be. I saw that option in the Start Time field but neglected it in the End Time Formula field. But you already knew that.
Thanks!