DATETIME_FORMAT is generating wrong Date

[Sali_Dimond]

First - I am pretty new to writing formulas. I know enough to get myself into trouble. :slightly_smiling_face:

Here’s my problem …
I have a table which is a listing of tasks that are assigned and have associated dated. The fields in this table includes:
Date field that includes the time field (Start Time)

I created an automation that would send an email when a particular person is added to a record (assigned a task). This email includes in the body of the email, the name of the task and the date and time that this task is scheduled to begin by inserting data from the Start Time field. The resulting email looked ok except that the data from the Start Time field was ugly (for lack of a proper technical term).

So I learned how to convert the data from the Start Time field into text by creating two new Formula fields using the DATETIME_FORMAT function:

• Field name: Date STRING // Formula: DATETIME_FORMAT({Start Time},‘MM/DD/YYYY’)
• Field Name: Time STRING // Formula: DATETIME_FORMAT(SET_TIMEZONE({Start Time}, ‘America/New_York’),‘h:mm’)

This was working fine for a while until a few days ago when I noticed that sometimes the date that was being created in the Date STRING field was a day later than the actual date in the Start Time field.

I then noticed that the records that had an incorrect output in the Date STRING field all had the same time set in the Start Time field of 7:00 PM. I then noticed that if I changed the time to an earlier time like 12:00 AM or 4:00 PM - all the way up to 6:59 PM - the output in the Date STRING field matched the date set in the Start Time field. But once that time was set to 7:00 PM, the output in the Date STRING field would not match; the output would be a day later.

I am guessing that there must be some kind of world clock issue happening.
But as I mentioned earlier, this anomaly only started a few days ago. Prior I had been having no problems with the output in the Date STRING field even for start times that were 7:00 PM or later.

What am I missing here?

And many thanks in advance.

Sali

[Jeremy_Oglesby · Accepted solution]

Sali_Dimond:

this anomaly only started a few days ago. Prior I had been having no problems with the output in the Date STRING field even for start times that were 7:00 PM or later.

:thinking: …hmmm… this seems like it should have been consistently off by a day if the time was after a certain time of day, because I’m pretty sure you are right about this:

Sali_Dimond:

I am guessing that there must be some kind of world clock issue happening.

Try this - open up the Field settings for the Start Time field and check the “Use the same time zone…” option:

That usually fixes issues with inconsistent dates/times being pulled into formulas:

If you are actually collaborating across time-zones with other people, however, this may have adverse affects. It’s easy to change back, so I’d give it a shot and see if it’s a workable solution.

[Sali_Dimond]

Eureeka!
Thank you so much, Jeremy.

All my collaborators are in my same time zone.

Sali