Need help with a formula to calculate end time bas...

[Jeffpdoes]

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.

[Kamille_Parks]

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

[Jeffpdoes]

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.

[kuovonne · Accepted solution]

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

[Jeffpdoes]

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.

[kuovonne]

[Jeffpdoes]

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!

[blaclair]

Hello! 

Does anyone know how to possibly prefill a Duration column? I'm currently working on an employee scheduling base and instead of having to manually input Start and End times I'm using the formula above. However, for breaks I already know they will be 15mins. I don't want to have to input that in ever time I do the scheduling. 

Any help would be greatly appreciated!