Answered

Excel Integration incorrect date format

  • 12 January 2022
  • 12 replies
  • 239 views

I’ve never had issues integrating into excel, but I now the date format is coming in like this and I can’t figure out how to fix it. Please help!

0022-01-16T00:00:00.000Z
icon

Best answer by john.desborough 14 January 2022, 04:55

View original

12 replies

Userlevel 7
Badge +5

Hi @BeckyCHP Thanks for stopping by! I believe this is expected behavior, though is this the date of the form submission or the date from the date field? 

Hi Liz,

Thank you for the response! It’s a date from the date field. We’ve been using typeform for over a year and I’ve never seen the dates come into excel in that format. It’s always been in this format 1/1/21 and that’s what we really need.

Userlevel 7
Badge +6

@BeckyCHP - it looks the ISO-8601 date representation that Excel announced that they would use about this time last year.. especially in Power Automate flows 

there’s a good blog post on this here at this link

One thing to note is that date fields are collected with a time stamp of midnight (hence the T00:00:00Z) which is UTC time 

you will have to update how  you deal with the date in Excel through formatting.. 

 

cheers

 

des

Thank you, I will check out the blog. That’s so strange that this is the first time we’ve run into this and we have multiple forms we’ve been using over the past year.

Userlevel 7
Badge +6

i noticed this on a form after an update by Windows this week… go figure.. lol

not sure myself but.. 

Userlevel 7
Badge +5

Ah, I didn’t know they announced that, @john.desborough . Thank you!

Userlevel 2
Badge +2

Unfortunately the blog isn’t really useful for Excel sheets that are receiving data from Typeform. I have one spreadsheet that works perfectly fine in OneDrive, but when I cloned that Typeform and then connected that to a 2nd Excel sheet, this data format error appeared. No change to cell formatting on the spreadsheet (or pivot tables) makes a difference. This is really a poor user experience.

Userlevel 7
Badge +6

@KDandenell - can you share some screenshots or a Loom/video of the situation? This sounds like an issue on the back end in Excel and not in the Typeform space.

 

des

Userlevel 2
Badge +2

I’m creating a brand new Excel connection to see if that clears the issue. Will report back!

 

Userlevel 7
Badge +5

Let us know how that goes, @KDandenell !

Userlevel 2
Badge +2

Yup - for some reason, removing and reconnecting Excel resolved the problem this time. I need to create another report so keeping my fingers crossed.

Userlevel 2
Badge +2

I have another data point - in both the new Excel connections, the error occurred when I copied over EXISTING data. After I deleted the connection and tried again, I didn’t bring over any data. The DATE format remained correct (e.g., MM/DD/YYY). Bug?

Reply