Transforming Data - formatDate

Unit introduction

Welcome to the third unit of “Transforming data using functions” in Make Basics.

By now, you should have built your first scenario utilizing the text functions. You will now build on this by exploring what you can achieve with date functions.

By the end of this unit, you will know how:

• to use the date function to change the format of a string to a readable date
• different services represent date formats
• to convert date/time to different time zones

Before you continue, make sure you’ve completed the exercise in unit 1 of this course.

Why format the date / time?

By default within Make, many of the modules will provide the date in a raw format, which may not be useful for your needs.

This format is known as ISO 8601, a globally recognized standard that helps the exchange and communication of date and time-related information on a worldwide scale.

Let’s have a look at an example of the date you have been receiving so far in your scenarios, and look at each component:

2023-03-06T14:34:54.742Z

• Year: 2023
• Month: 03
• Day: 06
• Date / time separator: T
• Hour: 14
• Minute: 34
• Second: 54
• Millisecond: 742
• Time zone designator: Z

The letter ‘Z’ is used to represent that the time is represented in Coordinated Universal Time (UTC).

Within your scenario, you may want to omit some information, or update it based on your location.

For example, the format MM/DD/YY is used in the US, DD/MM/YY is used in Europe, and YY/MM/DD is used in Japan.

Additionally, you can define how the separators of each component are displayed, for example /, ,, or -.

A note about organization / user time zones

By default, your scenario and functions will use the date/time from your Organization.

However, when reviewing scenario execution logs, all dates displayed to the user will be displayed in their time zone.

For example: Paula lives in the US, but works as part of an organization which is based in France.

Her scenario is scheduled to execute at 10AM France (organization) time, but the data displayed in the logs for her will show 4AM.

Unless the time zone is explicitly stated within the function, it will display by the organization time zone – this will be covered in the upcoming exercise.

‘Date’ vs ‘Now’

Deciding which date/time data item to map is important to ensure that your data is consistent. Let’s explore the options available within the upcoming use case: The Date from the Weather app, and the now variable.

Date (from Weather App)

Within the Weather app, there are modules that can provide a date string. From the Get daily forecast action, you can map the Date. However, the date will display in a format like 2023-03-07T12:00:00.000Z.

This is limiting, as it only displays the time as 00:00:00 (midday in UTC), regardless of when the scenario is executed.

Now Variable

There is an option for mapping dates consistently, regardless of which source app has been used: the now variable.

This variable is built into Make itself and is useful because it will provide the current date/time in GMT regardless of your location to the millisecond. For example, 2023-03-07T16:53:769Z will be displayed if you use now whether you are in London, Paris, Prague, or New York.

As a rule, the more data that you have to work with, the more data you can manipulate, and the more powerful your results will become.

Comparison and Recommendation

The key difference between the two formats is the output from the data type. The format of the Date from the Weather app is specific to that app. While Date will be offered in other apps, it may be structured differently. For example:

• Weather: 2023-03-07T12:00:00.000Z
• Online Form: 2023-03-07T06:33:40.000Z

The difference between these examples is that one (Weather) does not offer the accurate time (it’s fixed at midday UTC), and the other (Online Form) offers the time but potentially in an incomplete format.

Using the now variable will give you the current time in GMT to the millisecond. Plus, when you combine it with functions, it will allow you to log the exact date and time when a scenario was executed, which provides consistency across your scenarios. Therefore, you should always consider using the now variable.

The aim of the upcoming exercise is to show how date/time can be transformed and set to different time zones. As the Date from the Weather app will always show time as GMT 12:00, the time can never be accurately represented. Using the now variable will reflect the date/time to the nearest minute/second in GMT; so in order to localize this information to a time zone, it will need to be formatted using a function.

Here are both options with the same functions, executed at the same time at 13:28, on the 20th of April:

• Date (from Weather app):
  • Date and time: 2023-04-20T11:00:00.000Z
  • Date only: 20/04/2023
  • Date and time (Paris): 20/04/2023 01:00
  • Date and time (New York): 20/04/2023 07:00
• now variable:
  • GMT: 2023-04-20T12:38:00.100Z
  • Date only: 20/04/2023
  • Date and time (Paris): 20/04/2023 02:38
  • Date and time (New York): 20/04/2023 08:38

Transforming the date

With formatDate() you can change the input date to a format you want, such as transforming YYYY-DD-MM to DD/MM/YYYY.

For example, using the now variable in formatDate(): 2023-03-06T14:34:54.742Z can appear as one of the following: 06/03/2023, or 06.03.2023.

Using the now variable will also provide the time to the millisecond. Therefore the date can appear as:

06/03/2023 14:34, or 06.03.2023 14:34.

On the next slide, you will see a side-by-side comparison output of the Date from the Weather app, vs now.

Data accuracy - date vs now

Below is a side by side output of the Date / now in the same functions in the Weather app, and which data is accurate / incorrect.

You can never get accurate data on time with Date, as it will always show time as midday in UTC. Using the now variable will reflect the date/time to the minute/second in GMT. To localize this information to a time zone you want, use the formatDate() function and you will always get the correct result!

Here’s a comparison of the outputs:

When using Date:

• Date and time: 2023-04-20T11:00:00.000Z (Incorrect)
• Date only: 20/04/2023 (Accurate)
• Date and time (Paris): 20/04/2023 01:00 (Incorrect)
• Date and time (New York): 20/04/2023 07:00 (Incorrect)

When using now:

• GMT: 2023-04-20T12:38:00.100Z (Accurate)
• Date only: 20/04/2023 (Accurate)
• Date and time (Paris): 20/04/2023 02:38 (Accurate)
• Date and time (New York): 20/04/2023 08:38 (Accurate)

Before you start

It’s important that your scenario is configured before you start this exercise. Work through each stage.

Create a new scenario and call it Basic Functions - formatDate. This will be the same setup as the scenario that was created in unit 2: Add Weather → Get daily weather forecast, and configure it according to your location. Then add the Google Sheets → Add a Row module.

Within your Google Sheets - Add a Row module, select your Spreadsheet ID as Basic Functions - you should have created this Google Sheet as part of the exercise in unit 1. For the sheet name, select Date Functions.

If you have set up your Google Sheet correctly from unit 1, your headers should appear as follows:

• GMT (A)
• Date only (B)
• Date and time (Paris) (C)
• Date and time (New York) (D)

For the next exercise you will use each of these fields to map a different date, and adjust the format using the formatDate() function.

Formatting the date

This use case will give you an overview of how to utilize the formatDate() function.

In the Google Sheets module, click in the GMT (A) field. Instead of mapping directly from the list of items, navigate to the date/time functions tab and map the now variable. Click Run once, and you will see the current time in GMT reflected within your Google Sheet, for example - 2023-03-23T10:47…

Next, let’s use the formatDate() function to transform the date. Click the Date only (B) field. Navigate to the date/time functions tab again, and select the formatDate() function. This will insert a blank function for you, appearing as formatDate(). If you hover over this function, you will see a prompt that provides information about the function and how to map it.

Move your cursor to the space between formatDate( and ;. Click your mouse, and map the now variable, so it appears as formatDate( now ; ). This tells the function the source of what will be changed. Move your cursor between ; and ), and type DD/MM/YYYY. This specifies what you want the output of the function to be. It should look like formatDate( now ; DD/MM/YYYY ).

Click Run once and look at your Google Sheet. Notice the output of your date looks entirely different now. It should appear in the following format: 23/03/2023. Congratulations - you’ve just transformed your first date!

Notice that the time is missing? We’re going to add this now, as well as the location for a date/time. Open up the Add a Row module again, and click within Date and Time (Paris) (C). Follow the same steps as before. However, this time, add HH:mm ;Europe/Paris. When you are done, it should look like formatDate( now ; DD/MM/YYYY HH:mm ; Europe/Paris ). Ensure there are no spaces between ;Europe/Paris.

Click Run once – the time that appears will show the complete date and time for the location of Paris. E.g. 06/03/2023 10:44.

Finally, let’s repeat this step for Date and time (New York) (D). Map formatDate(now;DD/MM/YYYY HH:mm;America/New_York). Ensure there are no spaces between ;America/New_York. Click Run once. You will have a completely different time. The difference between the two times you have mapped should be 6 hours. This is because you are mapping the now variable, which shows GMT, but within a different time zone.

Definition: DD: Days MM: months mm: minutes

Have a look at your finished Google Sheet with your new mapped date/time items. This exercise has shown you how to format your date and time in the way that works for you based on your need and location.

Congratulations, you have successfully mapped the date using a function!