Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
turning "1 day, 1 hour, 30 minutes" into 1530 minutes
I have a column of cells that contain day, hour, minute which is used to
determine how long it takes for a technician to accomplish a task in the following format: 1 day, 1 hour, 30 minutes 1 day, 1 hour, 42 minutes 1 day, 1 hour, 52 minutes but also includes cells that look like this: 1 hour 1 hour, 11 minutes 1 day, 57 minutes What I would like to do is convert these cells into minutes only. Maybe set a variable that sets " day, " or " days, " to 1440 " hour, " or " hours, " to 60 "minute" or "minutes" to 1 and then multiply and put the result in the adjacent cell? Maybe I'm going about this the entirely wrong way, but any advice would be appreciated. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
turning "1 day, 1 hour, 30 minutes" into 1530 minutes
Try this:
Sub Tester() Dim arrQuant(1 To 3) As Long Dim arrUnit(1 To 3) As String arrUnit(1) = "day" arrUnit(2) = "hour" arrUnit(3) = "minute" LastRow = Cells(65536, 1).End(xlUp).Row For i = 1 To LastRow sTest = Cells(i, 1) For j = 1 To 3 UnitPos = InStr(1, sTest, arrUnit(j)) If UnitPos < 0 Then arrQuant(j) = Val(sTest) CommaPos = InStr(1, sTest, ",") If CommaPos < 0 Then sTest = Mid(sTest, CommaPos + 2) Else: Exit For End If End If Next j Cells(i, 2) = 24 * 60 * arrQuant(1) _ + 60 * arrQuant(2) + arrQuant(3) sTest = "" Erase arrQuant Next i End Sub HTH, Shockley "Joe Murphy" wrote in message ... I have a column of cells that contain day, hour, minute which is used to determine how long it takes for a technician to accomplish a task in the following format: 1 day, 1 hour, 30 minutes 1 day, 1 hour, 42 minutes 1 day, 1 hour, 52 minutes but also includes cells that look like this: 1 hour 1 hour, 11 minutes 1 day, 57 minutes What I would like to do is convert these cells into minutes only. Maybe set a variable that sets " day, " or " days, " to 1440 " hour, " or " hours, " to 60 "minute" or "minutes" to 1 and then multiply and put the result in the adjacent cell? Maybe I'm going about this the entirely wrong way, but any advice would be appreciated. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to specific "minutes:seconds" in cell format | Excel Discussion (Misc queries) | |||
hour minutes to minutes | Excel Discussion (Misc queries) | |||
"60 Minutes" Excel | Excel Discussion (Misc queries) | |||
With 30 minutes "work" millionaire?!? | Charts and Charting in Excel | |||
With 30 minutes "work" millionaire?!? | Charts and Charting in Excel |