JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.
We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.
Google Spreadsheet: How to keep Submission Date format as d/M/yyyy H:mm:ssAsked by john.roscoe on May 22, 2014 at 03:44 AM
This is another problem that has just occurred since the system "glitch" on 21 May.
Since that date the date/time format into Google drive cannot be reformatted to International format which we need. This appears to be the same problem that was fixed in December 2013.
In this screenshot the dates in red were before the glitch - formatted dd/mm/yyyy OK
But I cant change the format for any new submissions - it is locked as m/dd/yyyy
I have tried exporting to Excel but cant change the format there either, which imples that this field is now plain text rather than date/time format.
I thought I fixed this - by un-integrating the Google sheet, deleting it then re-integrating. All the existing values reverted to the correct format. However since then 2 new submissions have come in and the problem still exists - I cannot reformat the date/time value as stated above on the new entries.
Basically, you wan the format to appear as: d/M/yyyy H:mm:ss. Am I right?
If you noticed it, we've switch to the new google spreadsheet format so I'm not yet sure if the issue was cause or related by the new formatting or if it's because of the account timezone settings.
For now, you can do this in the Google Spreadsheet column to fix the format to: d/M/yyyy H:mm:ss format
Highlight the "Submission Date" column > Click "More Formats" in number button > Click "More Formats" in the dropdown options > Click "Custom Number Format" option:
Paste: d/M/yyyy H:mm:ss to the Number format box and click "Apply" button:
That should fix the formatting.
I am also forwarding this thread to our developers so they can further check and advise of what additional steps should be made for keeping this format.
Thanks for your prompt reply - however you haven't quite understood the problem
Yes I know how to reformat dates etc in Google but the problem is that the values DO NOT reformat any more. Its as if the values are plain text or the format is locked - now you cannot change the fomat to anything else at all,
for example if I apply format dd/mm/yy it should switch to date only but it doesn't change at all.
The only way I could make a change is by un-linking the spreadsheet, delete and re-integrate, then the new sheet shows all existing values in the correct format and they are also able to be reformatted. But when a new submission arrives in the sheet its date/time format is locked.
For info - I use Chrome and google spreadsheet configured for locale=Australia, Timezone=Auckland. This is consistent with my Jotform settings but changing these makes no difference.
Thank you kindly for the clarification about the actual problem at hand. My colleague has already forwarded this matter over to our developers so I am going to update his ticket about the new correct information of the physical problem you mentioned. This way our developers are aware of the locked formatting issue and we will update you as soon as we can.
Hello Mr. Roscoe,
Unfortunately I was not able to reproduce the problem, as by selecting the column, I am able to set format for the dates in spreadsheets.
If you don't mind, could you generate a new spreadsheet and share it with me so that I can see the issue?
Can you please re-look at this. The problem still exists as originally stated. If you try reformatting my spreadsheet you will have to look carefully to see the problem. Fortunately I also allocate a sequential barcode so I can sort the submissions into the correct order.
We needed date/time to resolve priority bookings but this is not possible when the date format wont sort (or display) correctly. It only sorts as if it were text, not a date. As stated before - if I unlink the spreadsheet and create a new one it formats all entries correctly, but all subsequent entries are in locked format.
I think this problem started when google updated to new sheets format, and as most of your customers probably use MMddYYYY format they wont be affected.
I have added another screenshot to show the formatting (and date value) issue from line 175 onwards.
Your message has reached my colleague @alp_deniz. He will get back to you once he is available online. As per his last message, is it possible for you to generate a new google spreadsheet and then share the URL with us?
We have recently enabled Time Format selection via Account > Settings > Time Format. You may now set it as you prefer and it will sort in the right way. Could you let us know if it is fixed or not?
Please accept my apologies for my late reply.
All the best
I am suddenly experiencing exactly the same problem as described above.
My Date/Time settings in Jotform are correct as DMY.
My locale in Google Sheets is correct as Australia. My cells in Google Sheets date column is correctly formatted as DD/MM
However, suddenly my February dates have switched to MM/DD format. In my Integrated Google spreadsheet that connects to my live form, all dates from Feb onwards display as mm/dd even though I have checked the format is correct. In fact, when I change the format to dd/MMM, it shows as 2 July 2017 instead of 7 February 2017.
I cloned the form and created a new Google Sheet integration and entered a transaction, but it also appears as MM/DD even though I have checked all Google Sheet settings for this column are DD/MM and Locale is Australia.
I have attached two snapshots (of the live data sheet and the newly integrated sheet to a cloned form to demonstrate the problem).
Is there a way you can dump the API sheet/data that Google Sheets is picking up to see what might be happening?
Here is the cloned form for testing: https://form.jotform.com/70371195093961
To better assist you, I have opened a separate thread for your concern. Please refer to this link instead: https://www.jotform.com/answers/1058678. We will address it accordingly.