Solved

# Convert HH:MM to numbers with decimals

Posted on 2014-12-19
113 Views
I'm trying to figure out how to convert hh:mm (hours and minutes) to numbers with decimals.  So for example:

01:00 would be 1.0 hours
01:30 would be 1.5 hours
00:45 would be .75 hours

etc., etc., etc.

Is there a formula for this?
0
Question by:SteveL13
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 3

LVL 120

Expert Comment

ID: 40509378
place this codes in a regular module

Function Convert2Dec(vTime) As Double
Convert2Dec = Val(vTime) + Split(vTime, ":")(1) / 60
End Function

then call the function
Convert2Dec("01:30")
0

Author Comment

ID: 40509390
I actually want to use it on a form.  So if Field1 is the original value I want to convert then Field2 needs to be the converted value.

How do I do that?
0

LVL 120

Expert Comment

ID: 40509409
me.field2=Convert2Dec(me.field1)
0

Author Comment

ID: 40509495
I'm getting an error:

Subscript out of range.

I have this in a module:

``````Function Convert2Dec(vTime) As Double

Convert2Dec = Val(vTime) + Split(vTime, ":")(1) / 60

End Function
``````

And I have this in the oncurrent event of the form that the two fields are on:

``````Private Sub Form_Current()

Me.txtConvertedHrs = Convert2Dec(Me.txtHrVariance)

End Sub
``````
0

LVL 120

Expert Comment

ID: 40509539
check the value of Me.txtHrVariance

also,  change the function to this

Function Convert2Dec(vTime) As Variant
If vTime & "" = "" Then Convert2Dec = Null: Exit Function
If InStr(vTime, ":") = 0 Then Convert2Dec = Null: Exit Function
Convert2Dec = CDbl(Val(vTime) + Split(vTime, ":")(1) / 60)

End Function
0

Author Comment

ID: 40509607

The value in me.txtHrVariance is 0:30  (and the format is Short Time)

I have this in a module:

``````Function Convert2Dec(vTime) As Variant
If vTime & "" = "" Then Convert2Dec = Null: Exit Function
If InStr(vTime, ":") = 0 Then Convert2Dec = Null: Exit Function
Convert2Dec = CDbl(Val(vTime) + Split(vTime, ":")(1) / 60)

End Function
``````

And I have this in the oncurrentevent of the form:

``````Private Sub Form_Current()

Me.txtConvertedHrs = Convert2Dec(Me.txtHrVariance)

End Sub
``````
0

LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 40509647
if it is time, just use

cdbl(Me.txtHrVariance) * 24
0

LVL 120

Expert Comment

ID: 40509648
Me.txtConvertedHrs = cdbl(Me.txtHrVariance) * 24
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
###### Suggested Courses
Course of the Month11 days, 12 hours left to enroll