MSQuery time conversion error

Topic

New Reply

I have an Access database that has the Time as text field (“0350” for 3:50 AM), which is recalculated in a query into a TimeValue decimal value. When I use Excel 2000 on a Windows XP computer MSQuery corrrectly retrieves the query’s time field as a decimal value (0.159722). When I use Excel 2010 to open the same spreadsheet on a Windows 7 computer, MSQuery transforms the value to “1899-12-30 03:50:00” which Excel changes to a 0 and displays in a time-formatted cell as 12:00 AM.

Is there some setting that needs to be tweaked in order to get the field to download properly?

Reply | Quote

Replies

I suspect the query is the problem.
Excel doesn’t really do time, it does dates which include time. As long as you tell Excel by means of formatting what you mean, it will work out the best solution. To do this I would have the query return a value of “03:50” and let Excel sort out what date to use. Using the colon will tell Excel to treat the value as a time. The alternative is to convert it to a number and format the cell as time.
1 in Excel = 1 day = 24 hours = 1440 minutes
03:50 = 180 + 50 minutes = 230/1440 days

cheers, Paul

Reply | Quote