Converting Time to a Number

[LouieGrandie]

In an Access database in building a table with a query I have to take a time, say 2:00PM and convert it to 14.00 or 9:48AM to 9.80. How can I do that?

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc

 

[SkipVought]

The Date/Time Value is in units of DAYS, so merely convert days to hours by taking the product of Time days and 24 hrs per day.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TheAceMan1]

How are ya LouieGrandie . . .

[blue]2:00 pm[/blue] is actually [blue]14:00 hrs[/blue] in military time (24hr). A simple format does the trick:

[blue]   Debug.Print Format(#2:00 pm#, "[purple][b]Hh:Nn[/b][/purple]")[/blue]

48 mins is 80% or 0.8 of one hour. Surely the math for this is plain ... I just don't see the need for such an display format ...

Perhaps if you supplied more data points it would become more plain.

10:00 am = [blue]?[/blue]
11:00 am = [blue]?[/blue]
12:00 am = [blue]?[/blue]
1:00 pm = [blue]?[/blue]
2:00 pm = 14:00

9:48 AM = 9.80
9:48 PM = [blue]?[/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Public Function decimalTime(dtm As Date) As String
   decimalTime = Format(Hour(dtm) + Minute(dtm) / 60, "0.00")
End Function

test

Public Sub testdtm()
 Debug.Print decimalTime(#2:00:00 PM#)
 Debug.Print decimalTime(#9:48:00 AM#)
 debug.print now
 Debug.Print decimalTime(Now)
End Sub

results

14.00
9.80
5/25/2012 2:15:52 PM 
14.25

 

[dhookom]

Why not just multiply the time value by 24.
#9:48 AM# * 24 = 9.8
#2:00 PM# * 24 = 14

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Why not just multiply the time value by 24.

I thought the OP's requirement was for a formatted two decimal place solution.

2:00PM and convert it to 14.00 or 9:48AM to 9.80

 

[dhookom]

I should have stated the formatting should be handled in the presentation (control or other).

Duane
Hook'D on Access
MS Access MVP

 

[LouieGrandie]

thanks for all the responses. Found out that Hour([Time])+Minute([Time])/60 will give me what I want. Have to round it after the conversion.

Visit Sage's Online Community

http://community.sagemas.com/sagemas/?category.id=SageMAS90and200Disc