Date formatting acting weird

[Jan_Gess]

I’m retrieving a date from a Google Sheets.

I want to add some days and subtract days from the date I get from Google Sheets.

Sometimes this works as expected, but sometimes it swaps the month and day.

In the example screenshots, I get 2 bundles with dates from Google Sheets:

• Bundle 1: 06/11/2024
• Bundle 2: 20/11/2024

In the next (set variables) module, I apply the addition and subtraction. Here the weird thing happens:

• Bundle 1: 06/11/2024 is interpreted at 11 June 2024 (incorrect)
• Bundle 2: 20/11/2024 is interpreted as 20 November (correct)

All other things are equal, so I don’t see how I can fix this.

Can anyone help?

blueprint.json (408.4 KB)

[Henk-Operative]

Hi @Jan_Gess,

Can you check and confirm that neither your Google Sheets column, your Make Profile (so your user) and your Make Organization are set in the wrong timezone? You can set a custom date and time format for your column in GS and normally the timezone you set for your Make user is the timezone that is shown in the UI.

If you trust that is OK, you can try to give the timezone as extra argument in your formatDate() function: https://www.make.com/en/help/functions/date---time-functions#formatdate--date--format---timezone--

Cheers,
Henk

[Jan_Gess]

No, the time zones are ok.
But I managed to solve it with a Parsedate followed with a Formatdate starting with the year.
Still not sure what went wrong, but it works now

[Msquare_Automation]

Hi @Jan_Gess

You have to do like this:

output:

If you wat the output in MM/DD/YYYY, Do as follows,

Best regards,

Msquare Automation
Gold Partner of Make
@Msquare_Automation

[dilipborad]

Hello @Jan_Gess,
As @Msquare_Automation suggests, always use parseDate and make sure you use the right set of formatted tokens(character) based on current format.

It will always works fine. There are no timezone issues.

I strongly recommend you also to check the official Make’s Academy course. It’s worth to check.

Make Academy: Course Overview

Hey Makers Kickstart your learning journey with our Make Academy! Today we’d like to share a summary of all the available courses. Enroll now, and learn the essential skills to learn automation at your own pace!

Click for basic information about the Make Academy

Every certification contains various courses and units. Each certification aims to upskill Makers with some of the fundamentals of Make. Make Foundation sets the groundwor…

[Jan_Gess]

Thanks everyone. It works now, but just out of curiosity:

If you look at my original screenshots, why is it that from the same Google Sheets, Make processes 2 lines differently?

One operation Make interprets the date field as DD/MM/YYYY, the other operation as MM/DD/YYYY.

[Msquare_Automation]

hi @Jan_Gess
If you pass a date like 06/11/2024 without specifying a format in Make, it will interpret the date in MM/DD/YYYY format by default. So, when you input 06/11, it reads it as June 11. However, if you enter something like 20/11, it tries to interpret it as MM/DD, but since there’s no 20th month, Make will automatically adjust, taking 20 as the day and 11 as the month. This is how the system handles date interpretation in the background. Hope that clears things up! Thank you.

Glad to know that your issue is resolved now. We would really appreciate if you mark the answer as solution if it helped you. This will keep the community healthy and organized. If you need further assistance please feel free to reach out.

Best regards,

Msquare Automation
platinum Partner of Make
@Msquare_Automation