converting time values to decimal 1

[marduk813]

I haven't been able to locate a thread that covers this, nor a suitable function in the help files, so here goes:

I need to convert a length of time into a decimal format. For instance, 1:34:45 (one hour, 34 minutes, 45 seconds) needs to be 94.75 minutes.

I've been looking at the TimeSerial function, but I'm not sure how to bend it to my will.

The Excel cells that I'm pulling the data from have been formatted to h:mm:ss. The worksheet is actually generated as a report by a 3rd party application, and I'm trying to create a custom worksheet using that data. (and using only Excel, not the 3rd party app)

Any suggestions?

 

[EBGreen]

Utested, but try:

strTime = "1:34:45"
lngTime = CLong(Split(strTime, ":")(0)) * 60 + CLong(Split(strTime, ":")(1)) + CLong(Split(strTime, ":")(2))/60
Debug.print lngTime

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[PHV]

Something like this ?
1440 * yourTime

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

 

[marduk813]

Tom, thanks for the reply, but unfortunately the data I'm pulling isn't a string data type. I've tried converting it using CStr, but it appears to convert it into a very, very, long number. (specifically, "6.57986111111111E-2")

Your code basically works as written (except CLong should be CLng), but i think a problem I'm going to have is that the values are showing up in Excel as actual times. For instance, "1:34:45" is actually "1:34:45 AM" in Excel. I anticipate a problem with times like "0:12:49" (which I need to read as 12 minutes, 49 seconds). Excel treats it as "12:12:49 AM". Does that make sense?

 

[marduk813]

Ok, I think I got it fixed.

PH, your code didn't quite get me to where I needed to be, since the time was showing up as a real date/time.

I was able to use a variation of Tom's code, but instead of converting to longs with CLng, I converted to variants using CDec.

First I formatted the value from the cell to a standard date/time format, then converted it to a string. Then I truncated the string to remove the " AM" or " PM", then checked to see if the first two digits were "12" (for 12 o'clock). Since Excel was displaying "0:12:49" as "12:12:49 AM", I needed to basically change the first "12" to "00". Then I used Tom's code to create one number, then rounded it to two decimal places to get the desired result.

This probably wasn't the best/most efficient way to do it, but it works for me!

Private Sub test()

    Dim strTime As String
    Dim varTime As Variant
    
    strTime = CStr(FormatDateTime(Worksheets("Sheet1").Range("H10").Value))
    strTime = Left(strTime, Len(strTime) - 3)
    If Left(strTime, 2) = "12" Then
        strTime = "0" & Right(strTime, 6)
    End If
    varTime = (CDec(Split(strTime, ":")(0)) * 60) + (CDec(Split(strTime, ":")(1))) + (CDec(Split(strTime, ":")(2)) / 60)
    varTime = Round(varTime, 2)
End Sub

 

[AnotherHiggins]

PH's formula will work fine. You just need to format how the answer is displayed.

format(YourTime * 1440,"0.00")

If you don't need to use code, you can use the worksheet function TEXT instead of Format.

BTW - the 1440 comes from multiplying 24 (hours per day) and 60 (minutes per minutes per hour)

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[AnotherHiggins]

I should have noted: if you use the TEXT, it will return a text string, not a number. If you want to go with a worksheet function, then you could use:
[COLOR=blue white]=format(YourTime * 1440,"0.00")*1[/color]
The "*1" will force the string back to a number.

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.