Solved

Convert HH:MM to numbers with decimals

Posted on 2014-12-19
8
100 Views
Last Modified: 2014-12-22
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
Comment
Question by:SteveL13
  • 5
  • 3
8 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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

by:SteveL13
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 119

Expert Comment

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

Author Comment

by:SteveL13
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

Open in new window


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

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 119

Expert Comment

by:Rey Obrero
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

by:SteveL13
ID: 40509607
No more error.  But no results in the field txtConvertedHrs either.

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

Open in new window


And I have this in the oncurrentevent of the form:

Private Sub Form_Current()

    Me.txtConvertedHrs = Convert2Dec(Me.txtHrVariance)

End Sub

Open in new window

0
 
LVL 119

Accepted Solution

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

cdbl(Me.txtHrVariance) * 24
0
 
LVL 119

Expert Comment

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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now