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

[Richardkhill]

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 go through the report and manually change the cell content to just hh:mm:ss.

e.g. if the report shows 1 days, 07:25:30, I manually change it to 31:25:30 by adding 24 to the hh reference. If the cell shows 2 days, 13:34:26, I add 48 to the hh reference to show 61:34:26

The cell is 'General' with no special formatting. 

Is there anyway I can get excel to do the conversation automatically by using a formula, macro or script? If needed, a new column can easily be inserted for the results. The results cell does need to be 'general' with no special formatting.

Thank You for your help 🙂

Richard

[#2 · Accepted solution]

@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.

[#3]

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.

[#4]

@djclements 

Thank You so much. Your second suggestion worked perfectly 

Richard

[#5]

@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.