Jan 08 2024 02:37 PM
Hello!
I need some help with a conversion on Excel. I have a column with data that goes down 25 rows. The values in those cells consists of numbers and texts that display a length of time. For example, "1 hours, 45 minutes". I am wanting to turn that information into a single value that is minutes. So the value will show "105".
If possible, can I get a step by step on how to get what I'm looking for? Keeping in mind that not all of the cells within that column are the same numbers, and some have no hours but will show "45 minutes".
I will attach a screenshot of the column looks like as well.
Thank you.
Jan 08 2024 02:48 PM
Let's say those values are in D2 and down.
In another column, enter the following formula in row 2, then fill down:
=IF(ISNUMBER(D2), D2, LET(parts, TEXTSPLIT(D2, " "), IF(COUNTA(parts)=2, --INDEX(parts, 1), 60*INDEX(parts,1 )+INDEX(parts, 3))))
Jan 08 2024 03:30 PM
You beat me to it! I took it further, though. After all, if a job is worth doing it's worth doing to excess!
= MAP(durationText, ConvertToMinutesλ)
"ConvertToMinutesλ"
= LAMBDA(text,
LET(
val, VALUE(TEXTSPLIT(text, " ")),
num?, ISNUMBER(val),
v, FILTER(val, num?, TRUE),
h, IF(COUNT(v) = 2, TAKE(v, , 1), 0),
m, TAKE(v, , -1),
time, TIME(h, m, 0),
TEXT(time, "[m]")
)
)
Jan 08 2024 03:33 PM