format time as decimal

[diwin]

I have a short time field in a table. Is it possible to display 10:30 AM as 10.5 and 10:30 PM as 22.5?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[Remou]

Perhaps:

Dim ti As Date

ti = #12:20:59 PM#
Debug.Print Hour(ti) + ((Minute(ti) * 60) + Second(ti)) / (60 * 60)

http://lessthandot.com

 

[diwin]

THanks.
Is it possible by formatting code, rather than running a procedure? Dateformat or sumpthink?

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[Remou]

You can use a single line, the code was there for descriptive purposes, for example, you could put this in a textbox:

= Hour(txtTime) + ((Minute(txtTime) * 60) + Second(txtTime)) / (60 * 60)

Where txtTime is the name of the field or control with the time.

http://lessthandot.com

 

[diwin]

the data will be imported from a csv file into a new table. The time format is... 18:12:28. I am looking for... 18.2

Daniel Dillon
O o . (<--- brain shrinking at rate shown.)

 

[Remou]

Let us say you link the CSV you wish to import:

SQL to create a new table called NewTable:

SELECT ImptCSV.Field1 AS ID, 
   Hour([Field2])+((Minute([Field2])*60)+Second([Field2]))/(60*60) AS TimDec, 
   ImptCSV.Field3 AS Other INTO NewTable
FROM ImptCSV;

http://lessthandot.com

 

[SkipVought]

There is no TIME format for fractions of an hour. You must CONVERT TIME, which is in units of DAYS, to HOURS. Hence, Remou's conversion formulas.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]