# Conversion Question

Copper Contributor

# 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 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.

4 Replies

# Re: Conversion Question

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))))

# Re: Conversion Question

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]")
)
)``````

# Re: Conversion Question

You can convert the durations into minutes in Excel using a formula that parses the hours and minutes from each cell and then calculates the total minutes. Here is a step-by-step guide on how to do it:

Split the text: If the text is in a consistent format (like "X hours, Y minutes" or "Y minutes"), you can use Excel's text functions to split the hours and minutes into separate cells.

Convert to minutes: after splitting the text, convert hours to minutes and add them to the minutes.

Handle cells with only minutes: ensure that cells with only minutes are handled correctly.

# Re: Conversion Question

Thank you so much this worked!