Excel 2010 importing time as 0

[automaker]

I have several spreadsheets that import DB2 records that include a time column (hh:mm:ss). The spreadsheets work in Excel 2003 & 2007. In Excel 2010, the time entries appear valid on the MS Query page. They are all 00:00:00 when brought over to the Excel page.

The column gets automatically formatted in Excel as hh:mm:ss, so Excel is recognizing the entries as time data (or is MS Query passing that column definition on from DB2?).

If I change the format to General, the value displayed is 0.

Since MS Query likes the data, and Excel "knows" the column is time, and this all works perfectly in old versions, why the heck are the values now being changed to 0?

 

[Jerry Sullivan]

Hi and Welcome to the Board,

A good troubleshooting step would be to see if the value is exactly zero or just a very small number being rounded to zero.

What happens if you format one of the Cells to Number with 16 decimal places?

 

[automaker]

I didn't check that. I'll take a look when I can get to 2010 at work on Monday. Since the time data is valid in the DB2 database and in MS Query I don't think I'll find a tiny decimal, but it's sure worth checking.

 

[Jerry Sullivan]

I didn't check that. I'll take a look when I can get to 2010 at work on Monday. Since the time data is valid in the DB2 database and in MS Query I don't think I'll find a tiny decimal, but it's sure worth checking.

I haven't done imports from DB2; however in reading your question, the possibility that came to mind is that the conversion from Number -> Time either: 1/1440 (1 unit=1 minute) or 1/86,400 (1 unit=1 second), is happening twice - from DB2 to MS Query and from MS Query to your worksheet.