Skip to main content

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.

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

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.

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!

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! 

Reply


Terms & Conditions