Excel - convert "1 hour, 20 minutes" to 1.33 1

[keun]

I get data like this:

0 hours, 7 minutes
0 hours, 44 minutes
1 hour, 53 minutes
0 hours, 1 minute

I want to convert it to decimals. Right now I use Text to Columns, Find/Replace to get rid of the hours and minutes text, convert the minutes to a fraction of an hour, and then add the two columns back together.

There must be a way to do this on one function. Anyone got any ideas?

I joined this forum in 2005. I am still a hack.

 

[dhookom]

I think you could use an expression like the following assuming the cell to convert is B2:

=INT(LEFT(B2,2))+INT(MID(B2,FIND(", ",B2)+2,2))/60

Duane
Hook'D on Access
MS Access MVP

 

[VulcanJedi]

Sub timefraction()
theColumnwiththeNumbers = 1

For x = 1 To Application.ActiveSheet.UsedRange.Rows.Count - 1
    theValue = Application.ActiveSheet.Cells(x, theColumnwiththeNumbers)
    
    theSplit = Split(theValue, Chr(32), , vbTextCompare)
    
    theH = theSplit(0)
    theM = theSplit(2)
    theD = Round((theM / 60), 2)
    theV = theH + theD
    Application.ActiveSheet.Cells(x, theColumnwiththeNumbers + 1).Value = theV
Next x





End Sub

 

[VulcanJedi]

IF you need to 'functionalize' it => Link

 

[keun]

=INT(LEFT(B2,2))+INT(MID(B2,FIND(", ",B2)+2,2))/60

wow

I joined this forum in 2005. I am still a hack.