Issue with UK date format (DD/MM/YY) and google sheets

Questions
,
1

:bullseye: What is your goal?

I’ve noticed that if I have a date stored in UK DD/MM/YY format or similar in a google sheet and then try to use this in a later module in Make that strange things happen.

If the date only makes sense if DD/MM/YYYY i.e. DD>12 then the scenario works as expected. However, if the date can work either way, e.g. DD<=12 then it changes the date to MM/YY/DD. This means a scenario can appear to work for months before suddenly going crazy.

As I understand it, google sheets stores the date as a UNIX Epoch and the formatting simply converts this. As such, I’m confused as to why this happens.

Worst case scenario, surely it would be better to throw an error is DD>12 rather than to operate inconsistently?

Even better, why not provide a regional setting / option to assume a default date format.

:thinking: What is the problem & what have you tried?

I assume a parse date would fix the problem but this makes the scenarios more complex if needed frequently.

4

Just got on this how about you try it this way

NEVER TRUST RAW DATE FROM GOOGLE SHEETS

You must normalize the date immediately after reading it

1 Like
5

Hey there,

the date will come in UNIX only if the cell is formatted as a date. If you are having issues with interpretation of dd/mm/yy as mm/dd/yy, then most likely, it is coming in Make as text and not date type variable.

Check you r scenario and see what the output is, and if its text, then use parseDate() on it to tell it that its dd/mm/yy.

1 Like