Rewrite the results of query to time

Question

I have the following code:

={"HOURS"; ARRAYFORMULA(IFNA(VLOOKUP(B86:B145,
{REGEXEXTRACT(""&'Sheet1'!D6:D, TEXTJOIN("|", 1, B86:B145)), 'Sheet1'!H6:H}, 2, 0)))}

This will return (some examples):

1h

1h 52s

2h 48m

3m 9s

5m

36s

so there are different results (the way it's formatted from Sheet1) h=hour, m=minutes, s=seconds

Is is possible to convert it to a time by editing my formula?

01:00:00

01:00:52

02:48:00

00:03:09

00:05:00

00:00:36

Sample sheet: https://docs.google.com/spreadsheets/d/1c3HdMq4PA50pYr88JqPoG51jvru8ipp8ebe4z5DczTQ/edit?usp=sharing

Answer 1

try:

={"HOURS"; 
 ARRAYFORMULA(IFNA(IF(VLOOKUP(B86:B, Sheet1!D:H, 5, 0)="-", "-", TEXT(TIME(
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B, Sheet1!D:H, 5, 0), "(\d+)h")), 
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B, Sheet1!D:H, 5, 0), "(\d+)m")), 
 IFNA(REGEXEXTRACT(VLOOKUP(B86:B, Sheet1!D:H, 5, 0), "(\d+)s"))), "hh:mm:ss"))))}

---

if those hours are durations use: [hh]:mm:ss

Answer 2

You can use the =TIME() function to convert values into a time. For example:

=TIME(13, 0, 52) would result in 1:00:52 PM. You can change the time format in the Format > Number settings if you don't want to include AM/PM, or want the leading 0 in the hour.

If you include a link to your spreadsheet, it would be easier to combine with the formula you already have to find a full solution.

---

Note: I may be completely overcomplicating this, but I got this method to work so I'm going to share my solution. I did attempt to combine most of these formulas with no luck.

Im going to go through how to set this up step by step to try to make this as easy as possible.

1. In Sheet1, place the formula =arrayformula(split(H6:H65, " ")) in cell I6.
2. In Sheet1, place the formula =arrayformula(SPLIT(lower(I6:I65),"qwertyuihmsopadfgjklzxcvbn``-=[]\;',./!@#$%^&*()")) in cell K6.
3. In Sheet1, place the formula =iferror(arrayformula(SPLIT(lower(J6:J65),"qwertyuihmsopadfgjklzxcvbn``-=[]\;',./!@#$%^&*()")),"-") in cell L6.
4. In Sheet1, place the following formula in cell M6.

=arrayformula(time(
if(iferror(search("h", I6:I65))>1, K6:K65, 0), 
if(iferror(search("h", I6:I65))>1, if(iferror(search("m", J6:J65))>1, L6:L65, 0),if(iferror(search("m", I6:I65))>1, K6:K65, 0)),
if(iferror(search("h", I6:I65))>1, if(iferror(search("m", J6:J65))>1, 0, if(iferror(search("s", J6:J65))>1, L6:L65, 0)), if(iferror(search("s", I6:I65))>1, K6:K65, if(iferror(search("s", J6:J65))>1, L6:L65, 0)))))

5. In MAIN, place the formula =ARRAYFORMULA(vlookup(B86:B145, Sheet1!D6:M65, 10)) in cell E86.
6. In MAIN, highlight the range with your time values (E6:E145) and go to Format > Number > More Formats > More date and time formats
7. Scroll down until you find 13:30:30. If you cannot find this in the list, reference the picture below to set the format manually.

Your times should now be formatted correctly. For aesthetic purposes, the columns in Sheet1 containing the intermediate formulas can be hidden if needed.

Spreadsheet