How to convert text like "1 day(s), 14 hour(s), 16 minute(s)" to time format?

I need to convert text that shows how old something is to a time format I can use for COUNTIFS to filter through data over a certain period.

Here are a few examples of the data:

1 day(s), 14 hour(s), 16 minute(s)
35 day(s), 6 hour(s), 17 minute(s)
14 hour(s), 7 minute(s)

I've thought about using TIME with LEFT, MID, RIGHT attributes but because it includes days if it's over 1 day, an doesn't always display 2 digits, I don't know how to achieve this.

2 Answers

Try

=IF(ISERROR(FIND("day",A1)),0,LEFT(A1,FIND("day",A1)-1))+0+TIME(IF(ISERROR(FIND("hour",A1)),0,TRIM(MID(" "&A1,FIND("hour"," "&A1)-3,2)))+0+0,IF(ISERROR(FIND("minute",A1)),0,TRIM(MID(A1,FIND("minute",A1)-3,2)))+0,0)

The result will be a decimal number with the digits before the decimal point the number of days and the decimals the time. Format to your liking.

Edit corrected formula to allow for value in A1 to start with single digit hour.

Here's an alternate approach to the problem to keep in your back pocket.

The time periods are expressed in days, hours and minutes. One or more time elements (days, hours, or minutes) might be absent. The time elements are separated by commas.

You can use Text to Columns to split the time elements into separate cells, so each cell contains a single time element, starting with an amount and ending with a units identifier. You peel off the amount, convert it to fraction of a day based on the unit, and add up the pieces. Then format the result as hours and minutes.

Text to Columns starts in the current cell, replacing the existing values, so if you want to preserve the existing values, copy and paste the data to a scratch area and let Excel parse it there. This is what it looks like after Excel splits it into columns:

I added a few records to cover different possibilities of time elements present. Note that the first element can be days, hours, or minutes, the second element can be only hours or minutes, and the third element can be only minutes. Column D is the result formatted as hours and minutes.

To explain the calculations, I'll add some columns to show what happens to each piece:

Columns F, G, and H are the time translations for the elements in columns A, B, and C, respectively. Column J is the sum in Excel time.

The formula in F1:

=LEFT(A1,FIND(" ",A1)-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"day","hour","minute"},A1))*{1,24,1440})

This peels off the number by finding the first blank space. It searches the cell to determine which time unit it contains, then converts the number to fraction of a day based on that unit.

The formula in G1:

=LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"hour","minute"},B1))*{24,1440})

This is similar except that the data contains space before the number, which must be trimmed.

The formula in H1:

=LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)/1440

Since the third element can only be minutes, we don't need to search for the units.

Each piece is now converted to Excel time and can be added up. However, there may be less than three elements present. Combining them in column J looks like this:

=IFERROR(F1+IFERROR(G1,0)+IFERROR(H1,0),"")

If the original cell was empty, this returns a null. If an element is missing, this replaces the resulting error with zero. The single formula in column D just replaces the cell references above with the formulas in the referenced cells:

=IFERROR(LEFT(A1,FIND(" ",A1)-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"day","hour","minute"},A1))*{1,24,1440})+IFERROR(LEFT(TRIM(B1),FIND(" ",TRIM(B1))-1)/SUMPRODUCT(--ISNUMBER(SEARCH({"hour","minute"},B1))*{24,1440}),0)+IFERROR(LEFT(TRIM(C1),FIND(" ",TRIM(C1))-1)/1440,0),"")