Working with Date/Time Fields

  • 7 July 2021
  • 4 replies
  • 5857 views
Working with Date/Time Fields
Userlevel 4

In a bit of a departure from normal Workflow Wednesdays, I wanted to talk about dates and common issues you might run into. Working with dates and times is always a bit tricky, especially when you’re including various systems that each want slightly different formats. 

Some fields will just accept whatever you send, but others have special symbols to indicate that we’re expecting a certain format of data. In this case, we’re talking about Date/Time fields, which have this icon:

OGqLq3p6WJ2NsnT9FbK88vbWaQHTFeTET8xhUVNpHYSjhW11fkzhq7N2mgZaDAuTtPCM5MGw_4t6isWJFwZ9IWXxXPh_QwOK2gU8c9MLu4dLwDkzmiwbBiPqrjlecqcwgS2nB4N9

Luckily, with these Date/Time fields, Zapier does most of this work on the back end! We’ll do our best to convert anything that even resembles a date/time into something that the specific app you’re using can read. That way, you don’t have to do a ton of special formatting just to get this working in the first place.

It’s easy for this system to get confused though! Here are some common scenarios and (hopefully) simple fixes.

The time is off by X hours - i.e. I want it to say 1:15 PM and it says 2:15 PM

99% of the time, this is a time zone issue. There are a few places that Time Zones can be set:

  1. In your Zapier account itself. You can change this in your settings, and it will reflect in all of your Zaps that don’t have a specific time zone already set. 

  2. In the Zap itself. Each Zap can have its own time zone set, and this will override the setting in your Zapier account. You can access that by clicking the gear on the right side of any Zap.

  3. In the timestamp itself. Let’s say you have a timestamp like this:

    Mon Jun 28 03:00:00 -0500 2021

    That “-0500” is indicating that the time zone is 5 hours behind UTC - this will override whatever you’ve set in your Zap or account, since we know exactly what time zone this timestamp is.

This can often be the source of the issue. Let’s say you want to create a Google Calendar event, and this is the time you are getting:

6/25/2021 3:15 PM -0500

We’ll convert that to a format that Google likes, and since the time zone is specified, we’ll make sure to send that along. However, if you got a time like this:

6/25/2021 3:15 PM

Now it’s not clear what time zone that is, and we have to guess! In the case of Google Calendar, this will depend on what time zone you have set in your Zap or in your account. If that’s not correct (or if the time zone changes depending on the data you get), then this could end up in the totally wrong spot.

In this case, the solution is to change the time zone of the Zap to match the Google Calendar, but it’s not always that simple. Sometimes, you might need a date formatter to specify the time zone - see below.

The date/time keeps being sent as the time I run the Zap

This is often because there’s an issue with how the time is formatted. I see this most often when someone gets a date and a time separately and maps both fields without a space between them:

r-VSAHd4oJpB1CY_xkNFL9sY7yEa2cuG3LYB1URJPwc5bEB32fqHpQvw73r1dMamPcBuwA-o3dbrWmU0vlJ9UDCgJI53zG1so_DBU4ZqbCYdKv9s1gx8aIEwCatqvVpPuwFFBSIw

(view larger)

This looks okay at first glance, but when I send it to Google Calendar, I get an event at exactly the time I run the test. This only happens sometimes - not all apps will do this! But when they do, it can be very confusing.

Imagine what this date and time looks like when we send it to Google Calendar. In this case, it would be:

28/06/202115:30

Hard to read, right? That’s why you need a space between the date and the time:

2UtOYhuTJMDcWK2TFkWPGilm2ju7nf_LxDbqbweQZvbByNzbto-9SFJYNSkbanZVut5IRnd4TTdQy8_zhz_zXMUqWfE3pWSrRUZHlo20wUEnilWYnNJdauKsOp0EEthC-aT4t5ut

(view larger)

A small difference, but it does exactly what we need it to now! 

So if you see this kind of error, make sure your date is formatted nicely for us.

The month and day keep getting reversed!

I see this happen most often when someone uses the DD/MM/YYYY format. Even though this is widely accepted as the standard, some apps still use MM/DD/YYYY exclusively. And if the date is ambiguous, we won’t know what to do with it when it’s not clear from the date which is the month and which is the day.

An example: Your Zap is working fine on June 30th, and the date you’re sending is this:

30/06/2021

We interpret that correctly and events are being created on the right date. Suddenly, on July 1st, the Zap stops working. This is the date that was sent:

01/07/2021

Now, all your events are being created on January 7th! That’s backwards because the date is ambiguous - we can’t tell by looking at it whether it is DD/MM/YYYY or MM/DD/YYYY. Depending on what the app asks us for, we might automatically convert it to the wrong format.

The trick is to use a date formatter to convert this to an unambiguous date. Remember how I said that Zapier will convert dates for you to whatever the app wants? That means we should be able to convert this to a human-readable format, and use that in the Zap later.

Here’s an example of a Date Formatter that will do this:

z2ue7Obzw4SoWjO82L1KxSebnvGcMrqZHVJMfdG1m-ARFe8TuYxVKafZz9Fb8TJk52FLoTKZiAlV1odAYi7PUSzCMzlEjlqX-49TUWjCyHZfxnfAj0gUODFs-KH_cjMRRQxl5cNO

(view larger)

By setting the To Format to something unambiguous (like MMMM DD YYYY HH:mm:ss), then ensuring the from format matches what we’re using (in this case, DD/MM/YYYY), then we’ll convert it to a totally unambiguous format.

You can also use date formatters to convert from one timezone to another, or to explicitly specify the timezone using various formats. You can even use custom date formats!

Those are the most common issues I’ve seen with dates - hopefully this helps you out!


4 replies

Userlevel 1

Hi Clint, great post!

 

My Zap is working really good thanks to all your contributions, the only remaining thing I need to figure out is how to set up an end date as +1 hour or +2hours from start date. This is how I´ve set things:

 

 

 

When the Zap runs, it works perfectly, but it shows the event in my calendar with the same time for start and end. Any suggestions?

 

Many many thanks

Erik

 

Userlevel 7
Badge +11

Hey @ErikGron,

Glad to hear Clint’s article here was helpful! :)

It looks like there’s a space missing between the time and the time modifier (+30m). If you add a space in between the two that should help to ensure the end time is different from the start time: 
547d748fdc1b6bf7b6999bf71427a81d.png
To set the end time to be 1 hour from the start time, try using the time modifier of +1h instead of +30m. And to set it to be 2 hours on, use +2h. You can find out more about using date/time modifiers here: Adjust date and time values in Zaps

Hope that helps! 

Userlevel 1

Hi Sam! 

I can´t believe that sometimes the solution is in front of my eyes. This solved 100% my issue, thanks a lot!

Have a great day

Erik

I’m running into a problem working with date serials coming from a spreadsheet. In one spreadsheet I have a date I have formatted to “YYYY/MM/DD” - which gets picked up as a serial in Zapier (45212). Running it through the Z date formatter, I get the correct results “YYYY/MM/DD”.

But during the next Zapier step where I post the date back to Google Sheets it converts back to serial. I see that when I go through the test of that step, the input comes correct, but the payload that gets sent has converted back to serial. 

Any thoughts about what I can do to fix this?

Reply