Importing Time Cell

[Vic Spainhower]

Hello,

I am using Excel Automation to load an Excel spreadsheet into an Access
Database. Unfortunately the way the spreadsheet is laid out I cannot use the
TransferSpreadsheet and I have to select particular cells. The cell the
contains a time value when imported comes across as a number such as
0.354166666666667 which represents 8:30 AM CT in Excel. Can someone tell me
how to get the time as formatted in Excel?

Thank You

Vic

 

[Douglas J. Steele]

Simply format the field. Just to demonstrate, here's the effect of using the
Format function in the Immediate window:

?Format(0.35416666666666667 , "Long Time")
08:30:00
?Format(0.35416666666666667 , "Medium Time")
08:30 AM
?Format(0.35416666666666667 , "Short Time")
08:30

Date/times are stored as 8 byte floating point numbers, where the integer
part represents the date as the number of days relative to 30 Dec, 1899, and
the decimal part represents the time as a fraction of a day. Realistically,
your 0.35416666666666667 represents 8:30 on 30 Dec, 1899:

?Format(0.35416666666666667 , "yyyy-mm-dd hh:nn:ss")
1899-12-30 08:30:00

However, Access is smart enough to drop the date most of the time:

?Format(0.35416666666666667 , "General Date")
08:30:00

 

[Vic Spainhower]

Thanks Doug,

I had tried Format(0.35416666666666667 , "Long Time") but Access simply
printed a 3. "Long Time" works much better.

Vic

 

[Vic Spainhower]

oops I ment vbLongTime is what I'd tried ~

Thanks Doug,

I had tried Format(0.35416666666666667 , "Long Time") but Access simply
printed a 3. "Long Time" works much better.

Vic