google-sheetstimegoogle-sheets-formulaarray-formulasgs-vlookup

Rewrite the results of query to time

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

Solution

  • 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"))))}

    enter image description here

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