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

  • Profile Image
    AgilityAssoc.Canada
    Asked on November 12, 2017 at 02:22 PM

    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

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 12, 2017 at 03:58 PM

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

  • Profile Image
    Welvin
    Answered on November 12, 2017 at 04:08 PM

    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. 

  • Profile Image
    ashwin_d
    Answered on November 13, 2017 at 12:10 AM

    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.

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 13, 2017 at 09:06 AM
  • Profile Image
    Nik_C
    Answered on November 13, 2017 at 12:09 PM

    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.


  • Profile Image
    AgilityAssoc.Canada
    Answered on November 13, 2017 at 02:58 PM

    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.

     

    1510603324dateerror.png

    Why is it flipping the month day values?

     

    Robert

  • Profile Image
    Nik_C
    Answered on November 13, 2017 at 03:50 PM

    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.

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 10:04 AM

    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

  • Profile Image
    Nik_C
    Answered on November 14, 2017 at 11:18 AM

    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!

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 11:22 AM

    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


  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 11:32 AM

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

    Robert

  • Profile Image
    Welvin
    Answered on November 14, 2017 at 11:35 AM

    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)? 

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 11:40 AM

    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.

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 11:46 AM

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

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 11:58 AM

    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

  • Profile Image
    Nik_C
    Answered on November 14, 2017 at 01:22 PM

    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.


  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 01:27 PM

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

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 01:37 PM

    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

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 01:51 PM

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

    1510685445date-error.png


  • Profile Image
    Nik_C
    Answered on November 14, 2017 at 02:26 PM

    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:

    1510687489Screen Shot 2017-11-14 at 8.28

    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.


  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 02:28 PM

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

    1510687677Date-Test.png


  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 02:34 PM

    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


  • Profile Image
    Nik_C
    Answered on November 14, 2017 at 03:25 PM

    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:

    1510690211Screen Shot 2017-11-14 at 9.12

    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.

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 04:59 PM

    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

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 14, 2017 at 06:06 PM

    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.

     

    1510701827excel-sheets.png

     

    Robert

  • Profile Image
    Kevin_G
    Answered on November 14, 2017 at 06:49 PM

    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. 

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 16, 2017 at 08:55 PM

    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

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 16, 2017 at 09:32 PM

    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 :) 

  • Profile Image
    Kevin_G
    Answered on November 16, 2017 at 11:00 PM

    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. 

  • Profile Image
    AgilityAssoc.Canada
    Answered on November 17, 2017 at 09:49 AM

    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

  • Profile Image
    Kevin_G
    Answered on November 17, 2017 at 10:16 AM

    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. 

  • Profile Image
    AgilityAssoc.Canada
    Answered on August 09, 2018 at 07:56 PM

    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

  • Profile Image
    Welvin
    Answered on August 09, 2018 at 09:42 PM

    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. 

  • Profile Image
    Nik_C
    Answered on August 10, 2018 at 11:32 AM

    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!

  • Profile Image
    AgilityAssoc.Canada
    Answered on August 18, 2018 at 11:26 AM

    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.

    1534605634Date-Fix.png

    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

  • Profile Image
    Nik_C
    Answered on August 18, 2018 at 12:49 PM

    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!