- mikemccloskeyAsked on July 19, 2010 at 09:34 PM
When I export form data into either Excel or CSV, the date (dob) field gets exported as "April 1, 2010". I would like the data to export as "04/01/2010". Is that possible?
Thanks. Great product!
- JotForm FounderaytekinAnswered on July 20, 2010 at 02:04 AM
No, it is not possible to change the date format. However, Excel is supposed to understand those dates and you can use Excel's date format to re-format them.
- thirdeyeAnswered on August 24, 2010 at 08:50 PM
When I use the Date Picker, the submissions or my Excel reports listed it as "August 24 2010" and I cannot change that to the Date Format. I need to do different filters and it seems like it's stored as text and I cannot change to date format.
Also, I have users to input a 18 digit number and when i export to Excel or CSV, the number is stored as 2.68435E+17 because it is a large number so Excel converted into exponential number and round up my original number. Can you make it export as text so the whole number would be reserved?
- JotForm FounderaytekinAnswered on August 25, 2010 at 08:22 AM
I think you can resolve both problems by selecting the column on Excel and then setting a particular number or date format for the column.
To describe how to do this, I made a form and test submission on it, and got an excel report:
I then right clicked on the number field and selected the Format Cells option:
Then set this column as a long digit of numbers:
This fixed the number formatting issue:
I did the same for the date column:
Once Excel gets the correct format for each field, the results can be shown as wanted or column can be sorted.
- thirdeyeAnswered on August 25, 2010 at 09:13 AM
Thanks for your fast response, but it still won't resolve my problems.
I don't have the problem with date on the date that the system auto generated. The problem I have is with Birth Date Picker field in my form. Those dates you won't be able to change by just changing the format of the date. It still always show August 24 2010 as a text field. Sometimes I cannot use the date that is auto generated, but I have to rely on the date that users input in the form.
You can change the format of the long number and it will display all the numbers, but if you look at the number again, it always ends with three 0's, because Excel rounds up the number. It does not keep the original number of the user input on the form.
Please look again. Thank you very much for your support!!
- JotForm FounderaytekinAnswered on August 25, 2010 at 09:45 AM
I see what you mean. I couldn't find an easy solution. The large number field needs to be set as a text field, and the format of the birth date needs to be changed. I added this to our bug list. We will look into it.
- Mike H.Answered on August 14, 2013 at 10:42 AM
After the data has been exported copy the date column and paste it into the Windows Notepad application. Select the pasted dates in Notepad and copy and paste them back into your Excel file and Presto!
The date format is now recognized by Excel.
- KadeJMAnswered on August 14, 2013 at 12:06 PM
Thank you for the additional information with a way of how to manually correct the date format for usage in excel. That is a good suggestion for a quickfix for this.