Google Spreadsheet: How to keep Submission Date format as d/M/yyyy H:mm:ss

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.

Hi John,

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

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?

Cheers

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.

Hello john.roscoe,

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?

Thank you!

Hello John,

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

Alp

@charitychallenge

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.

Thank you.