Forum Discussion
Conversion Question
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 ex...
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))))
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]")
)
)