Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Youngstown, Ohio, USA
    Thanked 0 Times in 0 Posts

    MSQuery time conversion error

    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?

  2. #2
    WS Lounge VIP
    Join Date
    Dec 2009
    Thanked 1,116 Times in 1,039 Posts
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts