Double to Time - can't be that hard!

[AKMonkeyboy]

I'm trying to convert various decimal values to times and feel my code has gotten out of hand - not to mention it does funny things at certain values.

For example, I'd like:
.1 to read 0:06 (.1 = 6 minutes)
.13 to read 0:07
.20 to read 0:12
etc.

I'm betting there's some nice code that will handle this, but I haven't been able to locate any - so I have built my own, but it does strange things with .20 and other values.

Hope someone has had experience with this previously and can direct me in the right spot.

Here's what I have so far (sorry it's so sloppy!)

    Dim intAfterDec As Double
    Dim intBeforeDec As Integer
    
    If IsNull(intTime) Then
        InttoTime = "0:00"
    Else
        intBeforeDec = Left(intTime, (InStr(intTime, ".") - 1))
        
        If intTime = 0.2 Then
            intAfterDec = 0.1 * Right(intTime, (Len(intTime) - InStr(intTime, ".")))
                If Len(intBeforeDec & ":" & Int(intAfterDec * 60)) = 3 Then
                    InttoTime = intBeforeDec & ":" & Int(intAfterDec * 60) & 0
                Else
                    InttoTime = intBeforeDec & ":" & Int(intAfterDec * 60)
                End If
        ElseIf intTime < 0.2 Then
            intAfterDec = 0.01 * Right(intTime, (Len(intTime) - InStr(intTime, ".")))
            
            If Len(intBeforeDec & ":" & Int(intAfterDec * 60)) = 3 Then
                InttoTime = intBeforeDec & ":" & 0 & Int(intAfterDec * 60)
            Else
                InttoTime = intBeforeDec & ":" & 0 & Int(intAfterDec * 60)
            End If
        Else
            intAfterDec = 0.01 * Right(intTime, (Len(intTime) - InStr(intTime, ".")))
            
            If Len(intBeforeDec & ":" & Int(intAfterDec * 60)) = 3 Then
                InttoTime = intBeforeDec & ":" & Int(intAfterDec * 60) & 0
            Else
                InttoTime = intBeforeDec & ":" & Int(intAfterDec * 60)
            End If
        
        End If
        
    End If

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[PHV]

Why not simply this ?
InttoTime = Format(intTime / 1440, "n:ss")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CautionMP]

AKMonkeyboy,
For minute precision: Decimal Time x 60 minutes per hour / 1,440 minutes per day.

Function ConvertDecimalTime(DecimalValue As Double) As Date
Const MinutesInDay As Long = 1440
ConvertDecimalTime = DecimalValue * 60 / MinutesInDay
End Function

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[SkipVought]

AKM,

As implied above, TIME values are in units of DAYS. Any value in units of hours, minutes or seconds must be converted to DAYS in order to be an Excel/VBA Time Value.

Skip,

_[red][/red]

 

[AKMonkeyboy]

But that looks too simple - don't you have anything more complex in mind?

Worked like a charm!

CautionMP - I tried your solution but it wouldn't handle null values...

Thanks again!

Monkey

Give a man a fish, and you feed him for a day.
Teach a man to fish, and you feed
him for life.
Send a man to Tek-Tips and the poor sap can find out how to fish on his own, and learn more by doing it.

 

[SkipVought]

"I tried your solution but it wouldn't handle null values..."

I once tried to divide by ZERO and it wouldn't handle it.

That's why you just might test a value before assigning it to a function.


Skip,

_[red][/red]