Output to Excel Formats Dates Incorrectly

djw8
djw8 used Ask the Experts™
on
I am running a proprietary Access application that outputs data to Excel for Analysis. I need to do some basic date computatations but I can't because I can't properly format the dates in Excel. I've tried several solutions without success. I've highlighted the column, right clicked it then tried to format it as a date, I've used the text to number, then tried to format as a date. That produced ########### characters which I understand is an undersized cell indication but I couldn't seem to make the cell large enough to show me the date. Is there a setting I can use that will make the date issue a little easier to manage? Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
djw8,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick
Top Expert 2010

Commented:
It would also be helpful to see the code from Access that produced the Excel output.

Commented:
Can you attach a sample of the Excel file to examine.
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Author

Commented:
Thanks, I can't provide the Access code, that's the proprietary roadblock. I am attaching the Excel File output, it's Excel 2007. The columns A-D are also included in the output and their date formatting isn't a problem. Column E represents the problem. eeex-051611.xlsx
Analyst Assistant
Commented:
How did you try Data>Text to columns?

I just selected column E, Data>Text to columns... and choose YMD for the column data format.

It converted the values to dates.

By the way, I know you can't post the code but can you change it, or request a change?

It might just be a simple fix in a query than rather the actual code that's needed.

Author

Commented:
I was looking at it and seeing YMD but it was actually DMY, my latent dyslexia kicked in, thanks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial