Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

DATETIME_DIFF and Timezones

Topic Labels: Dates & Timezones Formulas
795 1
cancel
Showing results for 
Search instead for 
Did you mean: 
mjungert
4 - Data Explorer
4 - Data Explorer

I built a task management table that tracks, start, due, and completion dates.

The start date is logged by an AT automation that pulls from a field called Todays Date. That field is set to PDT in the field options.

The due date pulls a unique SLA from a field using this formula:

 

{Start Date},DATETIME_FORMAT(SET_TIMEZONE(WORKDAY({Start Date},SLA),'America/Los_Angeles'),'MM/DD/YYYY h:mm A'),""))

 

The complete date is logged using the same automation method for start date.

Where I'm encountering an issue, is how early or late a task was completed. So duration of time before or after the due date. I've tried a few different methods, but I feel like this one should work:

 

DATETIME_DIFF({Complete Date},{Due Date},'hours')

 

The DATETIME_DIFF formula produces wildly inaccurate results. I've tried troubleshooting by adding an hours adjustment for GMT to PDT, or adjusting the units of measure down to seconds. The time difference inaccuracy doesn't seem to follow predictable pattern either.

Attached a picture showing some of the results. My assumption is this is an odd timezone management issue, but maybe my whole methodology is off. 

 

 

1 Reply 1

Interesting!  In "Due date copy", could you turn on the option to show the timezone of that field please?