- AgilityAssoc.CanadaAsked 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.
- AgilityAssoc.CanadaAnswered on November 12, 2017 at 03:58 PM
I tested the sheet after I changed the date format and it still shows it wrong.
- JotForm SupportWelvinAnswered 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.
- JotForm Supportashwin_dAnswered 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.
- AgilityAssoc.CanadaAnswered on November 13, 2017 at 09:06 AM
link is view only.
- JotForm SupportNik_CAnswered 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.
- AgilityAssoc.CanadaAnswered on November 13, 2017 at 02:58 PM
No the master sheet;
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;
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?
- JotForm SupportNik_CAnswered 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.
- AgilityAssoc.CanadaAnswered 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.
Thank you, Robert
- JotForm SupportNik_CAnswered 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.
- AgilityAssoc.CanadaAnswered on November 14, 2017 at 11:22 AM
Hi, yes I see that and I created a new form with import-range,
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
- AgilityAssoc.CanadaAnswered 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.
- JotForm SupportWelvinAnswered 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)?
- AgilityAssoc.CanadaAnswered on November 14, 2017 at 11:40 AM
OK, you now have edit rights to:
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.
- AgilityAssoc.CanadaAnswered 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.
- AgilityAssoc.CanadaAnswered on November 14, 2017 at 11:58 AM
I have given you edit rights to the integrated sheet.
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.
- JotForm SupportNik_CAnswered 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
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.
- AgilityAssoc.CanadaAnswered on November 14, 2017 at 01:27 PM
I have hidden a number of columns on TEST-CA sheet for viewing ease.
- AgilityAssoc.CanadaAnswered 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.
- AgilityAssoc.CanadaAnswered on November 14, 2017 at 01:51 PM
I actually imported your sheet and this is what appears when dates are formatted... errors
- JotForm SupportNik_CAnswered 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:
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.
- AgilityAssoc.CanadaAnswered 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.
- AgilityAssoc.CanadaAnswered 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:
It is open for editing. I'm convinced the problem is in the integration.
- JotForm SupportNik_CAnswered 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:
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.
- AgilityAssoc.CanadaAnswered 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,
- AgilityAssoc.CanadaAnswered on November 14, 2017 at 06:06 PM
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.
- JotForm SupportKevin_GAnswered 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.
- AgilityAssoc.CanadaAnswered on November 16, 2017 at 08:55 PM
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.
- AgilityAssoc.CanadaAnswered 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.
- JotForm SupportKevin_GAnswered 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.
- AgilityAssoc.CanadaAnswered 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
- JotForm SupportKevin_GAnswered 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.