How do I convert a numeric number to a customized text string?

Question

I have a list of numeric duration values:

1:00:00
3:00:00
2:30:00
4:45:00

that I would like to convert into text string values in a certain format:

xxh yym (h=hours, m=minutes)

So the above should look like:

1h 00m
3h 00m
2h 30m
4h 45m

I need to solve this preferably with a formula so I can convert 8k rows. What's the best way to go about this?

how do you want to convert 26:50:25 ? – player0 Jun 28 '22 at 19:13 — player0, Jun 28 '22 at 19:13

score 1 · Answer 1 · answered Jun 28 '22 at 15:31

1

You can do this with an array formula and the TEXT() method:

=arrayformula(text(A1:A, "h\h mm\m"))

Just replace A1:A with the range of time values you would like to convert.

Please let me know if you have any issues

answered Jun 28 '22 at 15:31

kaitlynmm569

score 0 · Answer 2 · answered Jun 28 '22 at 15:32

0

Well quick go at this:

But there are other ways.

answered Jun 28 '22 at 15:32

Solar Mike

2 Answers2