VBA Express : Excel - Converting hr:min to Decimal Hours on the Fly

Excel

Converting hr:min to Decimal Hours on the Fly

Ease of Use

Intermediate

Version tested with

2000,2002,2003

Submitted by:

lenze

Description:

The procedure uses DOLLARDE from the Analysis ToolPak to convert hours and minutes (3:45) to decimal hours (3.75) on the fly. It can be used in timesheets and other applications where time is used and calculations of total hours are needed.

Discussion:

The usual way times are used is to be entered as 3:45 (3hrs 45min) and then the result is multiplied by 24 with the result formatted as General, to produce 3.75. This also requires a second cell. Here we will enter the time with a simple difference and the code will convert the entry automatically, IN THE SAME CELL. So when 3hrs 45min is entered, it will be changed to 3.75 hrs. This allows for easy calculations

Code:

instructions for use

			Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    If Target.Count > 1 Then Exit Sub 
    If Intersect(Target, Range("myTimes")) Is Nothing Then Exit Sub 
    If Application.WorksheetFunction.IsNumber(Target) Then 
        Application.EnableEvents = False 
        Target = Evaluate("=dollarde(" & Target & ",60)") 
        Application.EnableEvents = True 
Else: 
        Application.EnableEvents = False 
        MsgBox "Invalid Entry. Entry will be cleared", vbOKOnly, "Opps" 
        Application.Undo 
        Application.EnableEvents = True 
    End If 
End Sub

How to use:

First, paste the code in the WorkSheet module.
Right click on the sheet tab and choose View Code and paste the code in the panel.
Return to Excel, ALT+Q.
Notice I have used a worksheet Named Range "myTimes" in the code. You may change this if you prefer or use standard cell references. In otherwords, you might replace "myTimes" with your own Named Range or a cell range ($A$1:$A$20). Just be sure to adjust the code.
Make sure the Analysis ToolPak addin is installed. Choose Tools>Add-Ins. If it's not selected, select it and hit OK. You may be prompted for a disc. Also, if on a network, you may need IT's help.

Test the code:

In your Worksheet, select a range of cells and assign them the name ("myTimes"). Insert>Name>Define. Enter myTimes in the Name field. Click OK. Note that you may omit the named range if you have chosen to use cell references.
Now begin entering times (3:45 or 1:12) in the Named Range with ONE EXCEPTION! Instead of time format, enter them as decimals and press enter. This means 3:45 will be entered as 3.45 and 1:12 would be entered as 1.12. When entered, the code will automatically convert the entry to Decimal Hours, so 3.45 will become 3.75 and 1.12 will become 1.20.
See example workbook.
Note also, that you can replace the "dollarde" in the code with "dollarfr" to go the other way

Sample File:

Time_Conversion2.zip 9.43KB

Approved by mdmackillop

This entry has been viewed 266 times.