Forum Discussion
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...
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.
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.