Forum Discussion

Richardkhill's avatar
Richardkhill
Copper Contributor
Nov 05, 2023
Solved

Covert text/number cell content to hh:mm:ss

Exported reports generated by a workplace system, record users total activity as 1 days, hh:mm:ss or 2 days, hh:mm:ss, etc. The report is usually over 300 rows long with multiple columns. I currently...
excel
Formulas and Functions
Office Scripts
  • djclements's avatar
    djclements
    Nov 05, 2023

    Richardkhill What does the report show if there's "0 days"? Does it show just the time (ie: 13:24:36)? Or does it still show "0 days, 13:24:36"? Either way, you can use a combination of the TEXTBEFORE and TEXTAFTER functions, wrapped inside the TEXT function to format the results as "[hh]:mm:ss".

     

    Example 1: "0 days, 13:24:36" format

     

    =TEXT(TEXTBEFORE(A2:A300," days, ") + TEXTAFTER(A2:A300," days, "), "[hh]:mm:ss")

     

    Example 2: "13:24:36" format (if 0 days)

     

    =TEXT(TEXTBEFORE(A2:A300," days, ",,,, 0) + TEXTAFTER(A2:A300," days, ",,,, A2:A300), "[hh]:mm:ss")

     

    Note: I've used range A2:A300 in this example for the range of values that need to be converted, and the formula was entered in cell B2. The results will spill for the entire range specified.

Richardkhill's avatar
Richardkhill
Copper Contributor
Nov 05, 2023

djclements 

 

Thank You so much. Your second suggestion worked perfectly :stareyes:

 

Richard

djclements's avatar
djclements
Silver Contributor
Nov 06, 2023

Richardkhill Just a heads up, if you prefer to reference a single cell at a time (which I see you have done in your screenshot with cell H11) and then copy the formula down to the bottom of the range, the TEXTSPLIT function would be simpler:

 

=TEXT(SUM(--TEXTSPLIT(H11," days, ")), "[hh]:mm:ss")

 

The main advantage of using the TEXTBEFORE and TEXTAFTER method, as shown in my previous reply, is that you can reference an entire range (ie: H3:H300) and the results will "spill" automatically with a single formula entered in cell I3. However, if you don't want to produce a dynamic array of results, the TEXTSPLIT function is much shorter in this case.

Resources