Convert Decimal Time Values to real Time values

[psycoperl]

Hi all,
I am importing from excel data that has times. When the data comes in some of the times seem to have been stored in decimal format (i.e. 0.3958333333 for 9:30AM). I am trying to find a way to test for this decimal format and convert it to a time value that can be stored in a Date/Time field on Access?

Thanks

 

[psycoperl]

Re: Convert Decimal Time Values to real Time values [SOLVED]

Hi all,
I am importing from excel data that has times. When the data comes in some of the times seem to have been stored in decimal format (i.e. 0.3958333333 for 9:30AM). I am trying to find a way to test for this decimal format and convert it to a time value that can be stored in a Date/Time field on Access?

Thanks

Ok. I found the solution testing with isDate() and if failing isDate() use CDate to create the time value in DateTime format. (see below)

If IsDate(rsISR![Time5]) Or IsNull(rsISR![Time5]) Then
rsASR![Time5] = rsISR![Time5]
Else
rsASR![Time5] = CDate(rsISR![Time5])
End If

 

[Joe4]

Re: Convert Decimal Time Values to real Time values [SOLVED]

Access/Excel both store dates/times as numbers. Specifically, it is the number of days since 1/1/1900. A decimal represents the time component, as all it really is is a "fraction" of a day.

So, you should be able to import the numbers "as-is" and just choose a valid date/time format to display them the way you want.

 

[xenou]

Re: Convert Decimal Time Values to real Time values [SOLVED]

It is somewhat curious as Joe is correct. This works fine for me:

Sub foo()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Table1")
With rs
    .Edit
    .Fields("TransTime").Value = 0.39
    .Update
End With
End Sub

If you find out a little more post back - there has to be something going on (could be in the data, the DB structure, or in the code ... or maybe I'm just crazy).

However, I do like using text representations of dates since different systems use different number systems to measure dates. I think the safest dates are internationally recognizable formats such as ISO 8061 format or something similar (importantly, to avoid m/d and d/m since this is ambiguous - jun 5 or may 6 are both valid interpretations of 6/5). If you use a strictly ISO compliant date format such as yyyy-mm-dd there is the added advantage that even as text the dates are sortable.