Convert decimal to time.

  • Thread starter turks67 via AccessMonster.com
  • Start date
T

turks67 via AccessMonster.com

Is it possible to convert 6.35 to 6:35 and be able to sort that column by
time order?

Thanks
 
P

Piet Linden

Is it possible to convert 6.35 to 6:35 and be able to sort that column by
time order?

Thanks
Click to expand...

You would have to treat it as text and then convert. Access does not
store dates as strings. It stores them as numbers. The integer is
the number of days from something like 1/1/1904 or something equally
seemingly random. The decimal is the time of day. So you could use
LEFT/RIGHT/INSTR to get the hours and minutes and combine them
correctly.
 
M

Marshall Barton

turks67 said:
Is it possible to convert 6.35 to 6:35 and be able to sort that column by
time order?
Click to expand...


You can sort numbers like 6.35 just as well as times like
6:35 so that is not a reason to convert them. If the time
field is really a text string, then just sort on an
expression like Val(tf)

If you have some other reason to convert that kind of
number, then use an expression like:
Int(tf) / 24 + (tf - Int(tf)) * 100 / (24*60)
or if your time field is a text field:
TimeValue(Replace(tf, ",", ":"))
 
T

turks67 via AccessMonster.com

Thank you Marshall Barton. Now I have to find out how to add AM and PM.
 
J

John W. Vinson

How are AM and PM depicted now? If 6:00 pm is depicted as 18.00, Marshall's
solution will work with no problems (and you can use the Format property of
the resulting date/time field to display what was 18.00 as 6:00 PM).
 
J

John Spencer

One more option:

Int(16.35) & Format(Int((16.35 - Int(16.35))*100),"\:00")
Will return a string 16:35

You can then change that to a time with the TimeValue or CDate. The
original value must never be NULL or you will get an error when you try
to convert to an datetime value.

CDate(Int(16.05) & Format(Int((16.05 - Int(16.05))*100),"\:00"))


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Top