Converting text based time values into usable numerical values

H

hn1981

New Member
Joined
Aug 4, 2015
Messages
1
Good afternoon everyone,

I need to update a massive spreadsheet that containts a large number of text based time values. I need to covert the text based time values into usable numerical values (in minutes). For example:

45 seconds into .75
18 minutes 45 seconds into 18.75
1 hour 7 minutes 45 seconds into 67.75

Any assistance would be greatly appreciated.

Thanks so much!
Hank
 
Last edited:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
well, this is a cumbersome solution. the hope is that all your time strings are in the same column. It creates a bunch of helper cells to separate out the numeric values from the string values. It converts the string values into factors that convert the numeric values into minutes. It assumes that time units are either "hour", "minutes", or "seconds". If you use "hours", for example it won't work. It could be modified to work but it wont work as shown. I'm sure the excel masters will have a more compact way of doing this so stay tuned.


Excel 2010
ABCDEFGHI
145 seconds450.01667    0.750
218 minutes 45 seconds181450.0166718.750
31 hour 7 minutes 45 seconds16071450.01667 67.750
45 hour 21 minutes 10 seconds560211100.01667321.167
5
6with a lot of help from Peter and others here:
7http://www.mrexcel.com/forum/excel-questions/594743-splitting-text-string-using-formula.html
8=TRIM(MID(SUBSTITUTE(","&$A2,",",REPT(" ",255)),COLUMNS($B2:B2)*255,255))
9with the "," replaced with " " because blanks separate the numbers from the units
10
11note, time units must be either hour, minutes, or seconds - other spellings or pluralities will generate an error
Sheet6
Cell Formulas
RangeFormula
B1=IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:B1)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:B1)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:B1)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:B1)*255,255)))))
B3=IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:B3)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:B3)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:B3)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:B3)*255,255)))))
C1=IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:C1)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:C1)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:C1)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:C1)*255,255)))))
C3=IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:C3)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:C3)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:C3)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:C3)*255,255)))))
D1=IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:D1)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:D1)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:D1)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:D1)*255,255)))))
D3=IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:D3)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:D3)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:D3)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:D3)*255,255)))))
E1=IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:E1)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:E1)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:E1)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:E1)*255,255)))))
E3=IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:E3)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:E3)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:E3)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:E3)*255,255)))))
F1=IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:F1)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:F1)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:F1)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:F1)*255,255)))))
F3=IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:F3)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:F3)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:F3)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:F3)*255,255)))))
G1=IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:G1)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:G1)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:G1)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A1," ",REPT(" ",255)),COLUMNS($B1:G1)*255,255)))))
G3=IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:G3)*255,255))="hour",60, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:G3)*255,255))="minutes",1, IF(TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:G3)*255,255))="seconds",1/60, TRIM(MID(SUBSTITUTE(" "&$A3," ",REPT(" ",255)),COLUMNS($B3:G3)*255,255)))))
I1=IF(COUNT(B1:G1)=3,VALUE(B1)*C1+VALUE(D1)*E1+VALUE(F1)*G1,IF(COUNT(B1:G1)=2,VALUE(B1)*C1+VALUE(D1)*E1,IF(COUNT(B1:G1)=1,VALUE(B1)*C1,0)))
I3=IF(COUNT(B3:G3)=3,VALUE(B3)*C3+VALUE(D3)*E3+VALUE(F3)*G3,IF(COUNT(B3:G3)=2,VALUE(B3)*C3+VALUE(D3)*E3,IF(COUNT(B3:G3)=1,VALUE(B3)*C3,0)))
H3=TRIM(MID(SUBSTITUTE(" "&$A3&REPT(" ",6)," ",REPT(" ",255)),COLUMNS($B3:H3)*255,255))
 
Upvote 0
Hi there,
this can be done with Power Query as well:

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
#"Duplicated Column" = Table.DuplicateColumn(Source, "Values", "Values - Copy"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column","Values",Splitter.SplitTextByDelimiter(" "),{"Values.1", "Values.2", "Values.3", "Values.4", "Values.5", "Values.6", "Values.7", "Values.8"}),
#"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Values.7", "Values.8"}),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Values - Copy"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Unit", each if Number.IsEven(Number.From(Text.End([Attribute],1)))= true then [Value] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Amount", each if [Unit] = null then [Value] else null),
#"Filled Up" = Table.FillUp(#"Added Custom1",{"Unit"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each [Amount] <> null),
#"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Amount", type number}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type1",{"Unit"},TimeTranslation,{"unit"},"NewColumn"),
#"Expanded NewColumn" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"multiply"}, {"multiply"}),
#"Added Custom2" = Table.AddColumn(#"Expanded NewColumn", "Minutes", each [Amount]*[multiply]),
#"Grouped Rows" = Table.Group(#"Added Custom2", {"Values - Copy"}, {{"Minutes", each List.Sum([Minutes]), type number}})
in
#"Grouped Rows"

LinkToFile

hth, Imke
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Need to add two sets of minutes with custom formatting and round
Replies
1
Views
532
bh24524
B
K
  • Solved
help with formatting a cell (for TIME, custom format) that retrieves its value from another cell...
Replies
2
Views
365
kbishop94
K
H
  • Solved
Formula Help - Time it takes to run 100 miles and then convert it to time of day
Replies
7
Views
550
Hankmardukaas
H
J
  • Solved
Sum up columns based on values (date and time) in more than one column
Replies
3
Views
294
myall_blues
myall_blues
A
  • Question Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
901
Aadya
A

Forum statistics

Threads
1,224,115
Messages
6,176,467
Members
452,728
Latest member
mihael546

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top