VBA - Converting Decimal Time to Hours and Minutes

[Sunjinsak]

Hi,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
I want to convert, if possible, a time expressed as a decimal value to hours and minutes in VBA.
<o></o>
Is there a way I can do this programmatically?
<o></o>
Just as a little background the calculation is to work out TOIL (Time Off In Lieu) and has to be performed in decimal hours.
<o></o>
The initial calculation is no problem, but I want to convert the results (expressed in decimal time) to standard HH:MM format to display to the end user.
<o></o>
For example:
7.4 hours = 7 hours and 24 minutes
9.5 hours = 9 hours and 30 minutes
5.02 hours = 5 hours and 1 minute
<o></o>
Also, for reasons too long to go into here, I have to use VBA. I can’t pass the results to an underlying worksheet and have Excel convert them, nor can I use Excel to do the calculations in the first place.
<o></o>
Can anyone help me with this?
<o></o>
Thanks in advance.

 

[pgc01]

Hi

To transform decimal hours to excel time just divide it by 24.

Ex.:

Sub Test()
Dim dbl1 As Double, dbl2 As Double, dbl3 As Double
Dim dt1 As Date, dt2 As Date, dt3 As Date
 
' 7.4 hours = 7 hours and 24 minutes
' 9.5 hours = 9 hours and 30 minutes
' 5.02 hours = 5 hours and 1 minute
 
dbl1 = 7.4
dbl2 = 9.5
dbl3 = 5.02
 
dt1 = dbl1 / 24
dt2 = dbl2 / 24
dt3 = dbl3 / 24
 
MsgBox dt1 & ", " & dt2 & ", " & dt3
 
End Sub

 

[Sunjinsak]

Hi pgc01,

Thanks for the reply but I'm a little confused.

7.4 / 24 = 0.3083

All I want is to be able to display 7.4 (as an example) as 7 hours and 24 minutes.

I just need a way to convert anything to the right of the decimal point to it's equivalent time value (in minutes).

Also, it has to be done programmatically - as stated earlier I can't use any Excel functions - only VBA.

Or do I have to first convert to 'Excel time' before performing the conversion?

Thanks.

 

[Sunjinsak]

Anyone?

Sorry to bump this - I'll only do it once - but I've been googling and searching forums for the past couple of days. I really need to be able to do this but I can't find out how.

I'm sure it's possible though.

Thanks

 

[gsbelbin]

You need to split the integer from the decimal part of each "time"

One way to do this is to use the Round function

Round(x - 0.5) & " Hours " & (x - Round(x - 0.5)) * 60 & " Minutes"

where x is your time (7.4 etc)

This is if you want to display the exact text you put in your question. If you want to display it as a time like 7:24 in a cell then use pgc01's suggestion. Yes you will get values like 0.3083 but you just need to format the cell to show it as hh:mm

 

[Sunjinsak]

Thank you gsbelbin that worked a treat!

And thanks for the explanation re the excel time - although it wasn't what I was looking to do it is useful to know.

Thank you both for your input. Much appreciated.