Forum Discussion
Covert text/number cell content to hh:mm:ss
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 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 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.
Thank you so much for all the replies 🙂
To cover some of the questions:
1. The cell doesn't have to be set as 'General'. I was just trying to confirm that it isn't formatted to anything specific (eg: Time: hh.mm.ss).
2. I have no idea what the spacing is between 'day' and the first number, so please assume its a normal value (ASCII 32).
3. If the total hours is less than 24, then the cell just displays hh:mm:ss (with no 'days').Hope that helps.
