CODEWITHSUNDEEP
datetimegoogle-sheetsutcvelo
Jana
Jana

Reputation: 1

Google Sheet to Wix date format issues

1I have a WIX site where part of a dataset is populated via an API in Google Sheets. The data runs through Zapier and then into the Wix dataset. I have no trouble with any number or text fields; however, I cannot get the date to come in as a format that works without a red flag in Wix.

The date format in the API is UTC: 2024-12-17T06:00:00Z

The red flag is an issue because when the data is displayed on a page and sorted by date, the dates with red flags cause errors. I can fix them by hand, but that is getting really tiresome.

I've attached a screen grab of the settings for the Date in the Wix dataset for reference. screenshot of Wix Date settings

It's important to know that all other inputs into the dataset, from forms on the site, return the proper date format without errors. Only the API results get a red flag.

I have tried the following, with corresponding results:

  1. UTC time and date: Sent as "2024-12-17T06:00:00Z", returns with red flag and "2024-12-17T06:00:00Z" I investigated via Wix help site and found they recommend a format with "mm/dd/yyyy hh:mm" My bad, so I went to step 2.

  2. I converted the UTC time to the recommended format with a google formula of =TEXT(A1, "MM/DD/YYYY HH:mm") Google Sheets result: "12/17/2024 00:00". Wix result was a red flag "1". When I asked Wix to reformat the date, the result was "Jan 1, 2001". Ugh.

  3. I'm not using the time portion when sorting or displaying results from the dataset, so I tried a formula in google sheets to remove the time (=LEFT(A1, LEN(A1)-10), then turned the result into a mmm dd, yyyy format (=TEXT(A2, "mmm dd, yyyy"). Google Sheets result: "Dec 17, 2024". Wix result again was a red flag "1".

  4. I'm out of ideas. Can you help?

Note: I can do the coding in Google Sheets to get various results (see below), but none seem to import into Wix properly.

API input Minus time reference Formatted to mm/dd/yyyy Added blank time in
LEFT(G28, LEN(G28)-10) TEXT(H28, "MM/DD/YYYY") CONCAT(I28, " 00:00")
2024-12-16T06:00:00Z 2024-12-16 12/16/2024 12/16/2024 00:00

Upvotes: 0

Views: 84

Answers (0)

Related Questions