Google Spreadsheet integration: Interprets the date as MM/DD/YYYY instead of DD/MM/YYYY

On 52745887899988, I have 5 date fields. When the integration occurs the sheet interprets the dates differently. I use dd/mm/yyyy and the sheet defaults to mm/dd/yyyy. So instead Jul 9, 2017 sheet shows  Sep 8, 2017. 

It is not apparent until I IMPORTRANGE and try and format the columns in the new sheet to; mmm dd, yyyy. I get some that don't appear to format and others that are wrong. I have over 200 lines that have this error. Is there any way to fix this?

I was thinking of changing the fields format to mm/dd/yyyy but that wont help with the 208 others. Do I just change it and forget about the previous? Will this solve the problem.

I tried changing sheet settings to United Kingdom instead of United States to get Day Month Year instead? But that did't fix it.

Any ideas? 

Regards, Robert

I tested the sheet after I changed the date format and it still shows it wrong.

You have the correct format in the date fields, and the spreadsheet should suppose to have the same format when receiving submissions. Instead of doing it in the form, I would suggest doing it in the spreadsheet. 

Select the whole column by clicking the column letter (1)

Go to Format (2) > Number (3) > Select the DD/MM/YYYY format from the list (4). If you cannot see this option, proceed to More Formats > More date and time formats (5). See screenshot below:

I hope this works. Please keep us posted. 

Hello,

I have tried changing the spread sheet format 10 different ways and it still won't do it right.

I even split the date into columns then recombined the numbers in a different order and it still gave the date wrong. i tried to change the sheet setup to a nationality that uses the format I want and it still messes up. 

before format

after, /08/ should be the month, and not all are seen as dates.

Vert perplexing...

Robert

I would suggest you to please share the integrated google spreadsheet URL and we will take a look. You may like to take a look at the following guide on how to share google spreadsheet URL:  https://productforums.google.com/forum/#!topic/docs/oqmg6zhY5JI 

We will wait for your response.

Hi,

link is view only.

https://docs.google.com/spreadsheets/d/1KR-TxZ7QjWNAm2iLDT3VS7aaixwB2Ff-x-gQ6NkaOl8/edit?usp=sharing

Robert

Thank you for additional information Robert, I checked your Excel and your form. I assume you did some formatting in the Excel since the dates are showing as DD/MM/YYYY. Also, I agree with my colleague that the dates in the form are showing as intended.

I did a test by cloning your form and integrating with my Spreadsheet, but I wasn't able to replicate this issue, the dates are showing the same like in the form, in the same format.

And according to formatting guides from Google (https://support.google.com/docs/answer/56470?co=GENIE.Platform%3DDesktop&hl=en) I must agree with Welvin, you can format dates in the Spreadsheet by selecting the area that you want to format and then through the formatting menu.

Hi Nic,

No the master sheet;

https://docs.google.com/spreadsheets/d/1KR-TxZ7QjWNAm2iLDT3VS7aaixwB2Ff-x-gQ6NkaOl8

When you first look at the date columns U,V,W,X in the master some values are left aligned and some right. This tells me that the sheet views some as text and some as number

I do no formatting. I then import the data to a new sheet;

https://docs.google.com/spreadsheets/d/1vnIBlm75Dra_WXsTMva5_zRywPjkpNOpXRnVuCjaKZg

Here is where I try and format the columns U, V W, X 

For the very first cell U2 of the Master sheet, it shows as 05/08/2017, but if you look in the entry bar (equation bar) at the top it shows 08/05/2017. I just now noticed this. So when it is formatted instead of Jul 08, 2017 you get May 05, 2017.

Why is it flipping the month day values?

Robert

Thank you for additional information Robert, I see what you are referring to. The first row is indeed changed and it switched the date and month. 

I tested several times but I wasn't able to replicate this issue, so I'm honestly not sure why that happened.

I'll try to check this additionally and what could be the cause.

I have created a copy of my form, /73156581308963, and integrated it. Right now I have no submissions. I would like one of you to use the form and then we will look at the sheet data. I have not changed the sheet formatting.

One thing of note, I modified the original form a number of times while it was in use, changing the dates format and from Normal to Lite mode. Could this have had an effect?

So I wait for your submissions and the results.

sheet

https://docs.google.com/spreadsheets/d/1vwTIBgJD5k2L8cs6YNs4jqn0MfsQLKldHf6gvG2uQ30/edit#gid=0

Thank you, Robert

Thank you for additional information Robert.

I just submitted your form two times.

No, such changes on the form shouldn't affect the integration and cause such behavior.

But, modifying the sheet could cause some irregularity and even breaking of the integration.

Please check the sheet and let us know how it looked.

Thank you!

Hi, yes I see that and I created a new form with import-range,

https://docs.google.com/spreadsheets/d/1gUmnnuB8YE_PlpJ26NS8MMtudC90592Khzm-_bS95do

I will share it with you and I have formatted the 4 columns with dates to mmm dd, yyyy and look at the results ... errors!

columns v w x y

Robert

Notice some are left aligned, seen as text, and right aligned seen as a number. Every dat is formatted incorrectly.

Robert

The formatting of the date should be identical from the original spreadsheet (source). Would you mind giving an edit access to emails with @jotform.com address? I believe this is all because of the formatting. 

Can you also try it with a test form and use the default three (3) date boxes (not lite mode)? 

OK, you now have edit rights to:

https://docs.google.com/spreadsheets/d/1gUmnnuB8YE_PlpJ26NS8MMtudC90592Khzm-_bS95do

When I created the second sheet the dates appeared exactly the way they do on the integrated sheet. I then formatted the columns.

I will edit the test form as indicated.

OK, I changed the form default for the dates. On the sheet every date comes in wrong.

I have given you edit rights to the integrated sheet.

https://docs.google.com/spreadsheets/d/1vwTIBgJD5k2L8cs6YNs4jqn0MfsQLKldHf6gvG2uQ30

As I said last, after I made changes to the form I submitted it. Every date is wrong on the sheets.

Why not make a simple form of your own. Integrate it, create a new sheet using =IMPORTRANGE and try formatting the dates. lets see the results.

Robert

Hello Robert, since I'm still not able to replicate this problem, I cloned your form again and integrated with Google Spreadsheet.

Here is the form: https://form.jotform.com/73175214042952

Here is the Sheet: https://docs.google.com/spreadsheets/d/1pryQ7st8wuxeayoq5Uq9-CnqzOTNALVVnCyUCiMMmfI/edit?usp=sharing

Maybe I misunderstood your workflow, so please correct me if I'm wrong:

1) The issue occurs in the Sheet without any previous actions in it, including modifying of any kind, formatting etc?

2) Importange that you use on the second Sheet, is not related to this issue since it happens after the issue already occurred?

Also, please submit my form and check the Sheet and let us know if you see any irregularity.

We need to narrow this down and see if the issue is related to our integration or Sheet.

Thank you very much for your cooperation.

I have hidden a number of columns on TEST-CA sheet for viewing ease.

The work flow you show is correct. I have made an entry and the dates appear as I posted them. Unless you format these columns we can't see if they are right or you need to create an additional sheet and import this sheet, then format the date columns.

Robert

I actually imported your sheet and this is what appears when dates are formatted... errors

That's where I'm lost, when you say you format, does that mean that you format the integrated sheet, like mine above, or is it some other one?

Also, are you doing this:

Since from initial post I understood that this issue occurs immediately without any actions, but then you mentioned the formatting and that's when the issue occurs, so I'm trying to understand that.

Thank you very much.

I made a totally new form with 4 dates. I integrated it. Then formatted the integrated columns. Here are the results.

As you can see above... I usually never touch the integrated sheet, but for the above I did. This tells me the integrated sheet is messed up to start with. When I format normally I do it on the imported sheet not the integrated, and I do it as you show.

The test form is 73175886108970.

the integrated sheet is:

https://docs.google.com/spreadsheets/d/159pORELz2NfaFjPgYC_fT35QBPD9YhUCGNA96rry9Wo

It is open for editing. I'm convinced the problem is in the integration.

Robert

Thank you for further info.

I think I'm finally there, the confusing part was I expected that the issue is happening immediately, but it is actually happening when you do date formatting, and not the ordinary one, but this:

The next thing is, that the issue occurs only for dates that are below number 12 (for day and month), so for example, for 7/8/2017, or 10/9/2017 etc. and that is when the switch occurs.

For example, for inputted date 28/10/2017 it will not even work.

With that said, I will forward this issue to our backend team for further checking.

Thank you for reporting this issue and assistance with troubleshooting Robert.

I'm sorry for all this back and forth emailing, but I had to be sure where and how the issue occurs.

Thank you again.

We'll keep you posted.

OK.. A lot of work but worth it. Interesting that one date actually came through OK because of the way the form was formatted. One thing as you noted about the values used, those that don't change into dates are formatted as text, that is evident by the way they align, left, any that show some date right or wrong are right aligned. this how spread sheets display information. I am going to down load the working form data to excel and see what happens.

In the mean time I wont do any formatting on the sheets and just leave things as they appear. 

You are very welcome and Thank you,

Regards, Robert

FYI

The excel sheet did much better with the dates on the most current submissions.

Trial Date was good from unique number 62 to present.

The other 3 columns are good from unique number 163 to present, 213.

Robert

Thank you for your update. 

This thread has been already forwarded to our second level so our developers will have a closer look on this. All the information here and future findings posted here will reach the assigned developer. 

We will keep you updated via this thread. 

Thanks. 

FYI,

I just remembered you are having a simular issue with your reports. I have a earlier thread reporting this issue. Visual-Report-builder-is-not-sorting-correctly. It to messes the date string and sorts incorrectly. These two problems may be linked. 

Just thought you might like to know.

Robert

I have a work-round. If I use the Mini-Date picker widget I get the date formatted exactly as I want on google sheets.  

YAHOO!

Robert :) 

I'm glad  to know you were able to find a workaround on this. 

That should work in the meantime the issue with the basic date picker is investigated and resolved, the other thread will also be checked. 

We will keep you updated on both threads. 

Thanks. 

Hi, I you telling me that the issue with the date picker and Google sheets is now fixed?

If so that is very good news. Thank you

Robert

No, sorry for the confusion, Robert. 

The issue is still being investigated by our developers, the ticked is still opened and we still need to receive updates from our second level. 

I will ask for  updates and will let you know as soon as I receive any. 

Hi,

Is there any update on this issue.

If not, can I get some feed back as to why it is not getting resolved.

This is not the first thread I have on the subject.

Makes me think it is not considered a problem.

Best Robert

Our apologies, Robert, but we do not have an update yet. I know you're waiting for this to get fixed, but it is still currently assigned to our backend team with its priority set to medium. I will try to ask our backend team for an update. I'll keep you posted. 

Hello... Good News

I think the issue is resolved.

A colleague of mine and I spent hours on Adobe Connect trying to understand why this was happening. 

1. First we realized the JotForm submits dates in mm/dd/yyyy, which is an american standard. 

2. The Canadian preferred format is dd/mm/yyyy.

3. G-Sheets standard is also dd/mm/yy

3. International format is yyyy/mm/dd. ISO 8601.

My account settings for date was d/m/y , now y/m/d . Doing this clears up issues with the submission date formatting on G-sheets so now there is no confusion between month and day values which was a major problem.

Using a test form we realized that all date fields should be set to yyyy/mm/dd, and for widgets also.

Making these few changes appears to have resolved the problem. The litmus test will be on 2018/10/13 (13 Aug. 2018). If all the dates come through as dates and not text we are off to the races and the case is closed.

I want to thank one and all who put up with my tenacity when it appears there was no problem with the system, it was just the settings. This has been a real learning experience and I am humbled.

Best Regards, Robert 

Thank you for sharing this information Robert, we truly appreciate that.

That is really a helpful information.

Making these few changes appears to have resolved the problem. The litmus test will be on 2018/10/13 (13 Aug. 2018). If all the dates come through as dates and not text we are off to the races and the case is closed.

Please let us know how the test went so we can update your backend team.

We'll wait for your response.

Thank you again!

Hi,

OK the results are positive and proved that there was an issue with the submission formatting.

All submission dates should be in the "yyyy/mm/dd" format.

The image below shows the date 1 column above the 13th as "yyyy mm dd" and it has the day and month flipped so on the 13th of Aug. it is text because there is no 13th month.

Then after it was formated at the form for "yyyy/mm/dd", on the 14th it corrected and is now a date and not text from there on.

Column Date 2 was formated for yyyy/mm/dd and displayed as dd/mm/yyyy and works fine right through.

I think this is proof positive that formatting at the form source is very important.

Thank you for your help in this very long issue which is now closed.

Best Regards, Robert

Thank you, Robert, for this information and let us know, we truly appreciate your contribution to this issue.

We'll close this ticket, if you experience any further issues, please let us know.

Thank you!