Exporting as CSV or saving record as spreadsheet does not yield consistent date formatting in excel

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I'm attempting to export a record/report as a CSV or save the same as a spreadsheet so I can use formulas in my "Sheet 1" to reference the exported cells in "Sheet 2." The problem I'm running into is that QuickBase seems to randomly change the format of the date when it appears in Excel. I've tried inputting dates via typing/manual input and selecting dates from the calendar button and then exporting to see if that makes a difference. I've found that it does, but in one column, the format is mm/dd/yyyy and in another column it exports as mm-dd-yyyy. Other issues I've run into are it exporting as dd-mm-yyyy and various other date formats, which are useless unless I manually change the cell date format in Excel for each export. I've attached a PDF that illustrates what I'm talking about. I need QuickBase to export in a consistent format, preferably dd/mm/yyyy. Thanks.

Photo of Evan

Evan

  • 20 Points

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
I believe that you need to change your WINDOWS default date format to match the typical QuickBase MM-DD-YYYY

I think what is happening is that plausible but incorrect dates are being treated as dates and the implausible a one like month number 13 are being treated as text.

I'm not at a Windows computer now, so I can't give you the exact steps, but maybe google words default date format.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
I re-read your post and I see you are wanting DD-MM-YYYY.   But I think the solution will lie in the windows setting.
Photo of Robert

Robert

  • 0 Points
Hi Evan,

Are you UK based (or at least, somewhere where the "normal" date format is DD/MM/YY)?

You can set your application to use DD/MM/YY (In APP Settings | APP Properties | Formatting | Dates) so that the date order inQB is as required, but more importantly Excel exports are correctly formatted. We've suffered with this for years in our apps, and still have legacy apps where this is an issue.
Photo of Evan

Evan

  • 20 Points
The date format used is MM-DD-YY.

I have changed the Windows date and time settings and that seems to have rectified the format conversion issue.

Thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Thx for letting me know.  That one is tricky because the normal thinking is that the problem lies with an Excel setting or perhaps  a QuickBase setting.
Photo of Stewart

Stewart

  • 0 Points
That's OK if you want your default Windows settings to be MM-DD-YYYY but if your Quickbase format is MM-DD-YYYY and you want to view (and export) dates in DD-MM-YYYY format I don't know what the work-around is.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Did you try changing your windows settings?  Are you saying that you want your windows settings to be different from your QuickBase settings?
Photo of Stewart

Stewart

  • 0 Points
Thanks for the response- No I did not change my Windows settings as I do not want dates to be formatted as MM-DD-YYYY .  Yes I want my Quickbase settings which are now MM-DD format to be different to Windows settings which are DD-MM.  As the date preference is regionally based it's not an option to universally set the QB settings to DD-MM.  I hope that explains the situation.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
OK, I suspected that was the case.  I'm not sure that the solution is.