Formatting dates inside Google Sheets

I’m trying to format the date in a custom format for readability.

I tried the following: Mo MMMM, YYYY k:mm a

Example:


{{formatDate(1.date; Mo MMMM, YYYY k:mm a)}}

But got an error:

The operation failed with an error. Failed to map ‘values.0’: Function ‘formatDate’ finished with error! ‘September 17, 2023 at 3:44 PM GMT+12’ is not a valid date

I assume I’ve done this incorrectly?

If you’re using formatDate, make sure the variable type of the field or cell accepts a string, not a date.

Also make sure that 1.date contains a Date type, not a date-like String. If it’s a date-like String type you’ll need to convert that into a Date type before you can use formatDate. For more information, see Wrong formatDate - #4 by samliew

2 Likes

Not sure I follow, its going into a google sheet. Do I need to change anything I need to change in Google sheets?

Also attached the variable in my chatbot, currently its set to “string”.

Plus the other type options the variable can be set to.

Screenshot 2023-09-30 121310 - Copy

That looks like a date-formatted string type, so it’s not actually a date type variable, and you cannot format a string type.

A hint is that variable date types do not contain ... at ....

So you’ll need to use parseDate first to make it a date type, using the Tokens.

e.g.:

formatDate(parseDate(1.date; <original format here>); <target format here>)
2 Likes

Thanks! it worked perfectly

My method is:
{{formatDate(parseDate(1.date; "F j, YYYY at h:m A T"); "Do MMMM, YYYY h:m a ")}}

2 Likes

For some reason the day and month are coming through wrong, I tested this yesterday and the output in the Google sheet was: 1st January, 2023 7:18 pm

This is my format:
{{formatDate(parseDate(1.date; "F j, YYYY at h:m A T"); "Do MMMM, YYYY h:m a ")}}

Looking at my testing history, it keeps repeating the 1st January day and month.

F and j are not valid month and day tokens. Please refer to the date format tokens again.

3 Likes

Thank you, sorry I must have missed updating those two tokens.

3 Likes