Download to Excel corrupts dates

Hi

I've noticed that sometimes the dates are one way, and sometimes another - I didn't really give it much thought! But, I have been able to adjust the dates in excel by selecting the column, changing the type to date, and formatting in the ddmmyy format. Not sure if this will help you?

But, I've also noticed another - dare I say - bug when exporting to excel - the file sizes are absolutely HUGE, and the only way around it is to copy the exact date you require (not the whole sheet) and paste into a new file. Anyone else noticed this?

I heard about not bad application-Excel recovery tool, recover this files after viruses, problems with media (a hard drive failure, a corrupted CD or DVD, etc.), restore files next types *.xls, *,xlt, *.xlsx, xlsm, *.xltm, *.xltx, *.xlam.

Hi Bexc, thanks for trying but if you read my posting you will see that your solution has been tried and is not working - at least not for me. By changing the date format to dd/mm/yy, the error is consolidated and there is no way back other than retyping the date u.e. switching round the day and month.

How does one get this bug to the attention of the developers?

Quote:
"When downloading QB reports to Excel, the dates are corrupted.
The date appears in Excel as dd/mm/yy only if the value of dd is greater than 12.
If the value of dd is equal to or less than 12, the dd and mm are switched and there is no way of recovery other than re-typing the date.

Example:

17/04/2008 appears correctly as the 17th April 2008. The date is left justified, i.e. text and does not accept different date formats.
09/04/2008 appears incorrectly as the 4th September 2008. The date is right justified and different date formats can be applied.

This is clearly a bug. Has anyone got any solution for this?

Maren"


First make sure your regional setting under the windows control panel option is set correctly to the regional date format you require probably UK.
Qb takes its date format from the windows regional settings. It just might be they are mismatched.

I have seen Graceman's suggestion, and I don't think that changing regional settings in Windows Control Panel works.
My region has always been set to UK: language, dates, times and currency are all set to UK. The Excel export problem still persists. I have to admit that I am using QBPro 2003 (updated, but still old!), but it seems that this has never been solved.

I presumed that it was a formatting problem somewhere, in that the export to Excel included dates in US format, but because my system was set up for UK, the dates were not being read correctly upon arrival into Excel. There is a similar (though not as bad) problem on other types of data exported to Excel, where you get messages about items 'that look like numbers being treated like numbers'.

I still reckon this is a bug in the QB export process.
Would the most desirable thing would be for users to be able to set their report (export) field formats in QB in the individual report set up pages (Modify report)?
This would be useful because that way one could then memorise the same structure report in different ways - one for print, one for export to spreadsheet.

Hi Folks,

Hurrah, I've cracked it.

1) Before downloading to Excel, set your regional settings to US.
2) Download your file.
3) Re-set your regional settings to UK.
4) Select the date column and widen it to see if it worked. All dates should have the same justification.
5) Apply your preferred date format to the column.

NOTE: It is still a bug in the QB export routine and I hope someone will fix it because it is a real efficiency hindrance. Not business friendly at all!!!

Hi there,

If you are still having a problem with the dates, here is an Excel formula which converts the date as extracted from Quickbooks into Excel date format (presumes the cell with the extracted data is A1) :

=IF(DAY(A1)>12,DATE(YEAR(A1),MONTH(A1),DAY(A1)),DATE(YEAR(A1),DAY(A1),MONTH(A1)))

If you just get a number, you will need to format the cell to the required date format.

Hope this is of use.

Tony

Hi Tony,

your solution works, thank you.

Do you know if similar problems exist with QB versions later than 2002 ? I don't need any of the newer functionality, but if I can be assured that this bug has been fixed, I may upgrade.

Thank you for your help.

Hi Maren,

This date problem occurs on all versions of Quickbooks up to 2004. 2005 and 2006 are ok, but the problem reoccurs in the 2008 version.

Tony

Quote:
"When downloading QB reports to Excel, the dates are corrupted.
The date appears in Excel as dd/mm/yy only if the value of dd is greater than 12.
If the value of dd is equal to or less than 12, the dd and mm are switched and there is no way of recovery other than re-typing the date.

Example:

17/04/2008 appears correctly as the 17th April 2008. The date is left justified, i.e. text and does not accept different date formats.
09/04/2008 appears incorrectly as the 4th September 2008. The date is right justified and different date formats can be applied.

This is clearly a bug. Has anyone got any solution for this?

Maren"

At work with Excel files recommend try this tool-recover Excel files,tool is free as far as i know,program recover Excel information if somehow it was corrupted,can happen due to power failure and lead to file system corruption, it means, that all of your documents, including Microsoft Excel files, will be lost,restore all of your Microsoft Excel documents in several minutes and save many hours or even days of your precious time for .xls recover,recover software cannot cover entire document, when it is damaged too various, recovered Excel file not compatible,but in the most cases, all documents are repaired successfully in automatic mode.

This bug still exists in 2008 release.

Just as frustrating is the inability anywhere in Intuit, to report BUGS. They have an iron curtain behind which all support advisors work, and they will not accept bug reports unless you buy a "support plan".

Why, for instance on this community board is there not a "bugs" section Intuit? This excel export bug is a prime example, clearly there for years, clearly a significant bug, and clearly beyond sight of the developers.

PT

This has also happened to me with Quickbooks Pro 2010 and I do have my preferences set for the UK so Graceman's solution was of no use.  However, what seemed to work for me was to merely reformat the date column in Excel by selecting 'Format','Format Cells' and then 'Date' and changing the local to "English - United Kingdom". 

Took me a long time to figure this out unfortunately having messed around with different formulas etc.  It's a pity Quickbooks has these drawbacks.  Pretty shoddy.