# Convert time format 1w1d1h1m to number of hours in Excel

I have text in the format "1w1d1h1m" in an Excel workbook.

Is there any way that Excel can convert this time format into number of hours?

-

Assuming that weeks will be any number of digits, days 1 digit (0 to 6) and hours and minutes up to 2 digits this formula should work for any combination of w, d, h, m being present

`=IFERROR(LEFT(A1,FIND("w",A1&"w")-1)*168,0)+IFERROR(MID(A1,FIND("d",A1)-1,1)*24,0)+IFERROR(LOOKUP(10^3,MID(A1,FIND("h",A1)-{1,2},{1,2})+0),0)+IFERROR(LOOKUP(10^3,MID(A1,FIND("m",A1)-{1,2},{1,2})+0)/60,0)`

-
th for the formula. I needed originally for working days & hours, so I adjusted it to accept week as 5 days and day as 8 hours. Works like charm. Thanks. – Jaroslav Urban Jun 5 '13 at 16:08

A formula for this could be quite lengthy (as @chuff has just shown!) but if it is guaranteed that letters will never be immediately adjacent then I'd suggest replacing w by m, d by m, and h by m then Text to Columns using m as the delimiter and finish off with a formula `=168*A2+24*B2+C2+D2/60`, assuming your date started in `A2` (can be copied down).

-
If missing any of the letters, add an extra m for those that are missing (1w11h >1mm11m) and do not check Treat consecutive delimiters as one. – pnuts Jun 3 '13 at 17:35

Assuming that the time value you are working with is a string, the following formula should do it:

``````=IFERROR(LEFT(A1,SEARCH("w",A1)-1)*168,0)+IFERROR(MID(A1,SEARCH("w",A1)+1,SEARCH("d",A1)-SEARCH("w",A1)-1)*24,0)+IFERROR(MID(A1,SEARCH("d",A1)+1,SEARCH("h",A1)-SEARCH("d",A1)-1),0)+IFERROR(MID(A1,SEARCH("h",A1)+1,LEN(A1)-SEARCH("h",A1)-1)*(1/60),0)
``````

If any of the elements of the time string are missing, then that time category is counted as 0.

-
Appreciate help, but it didn't work for me. I did not change formula and just typed "1w11h" in A1 field. It just displayed an error. – Jaroslav Urban Jun 3 '13 at 16:46
@Jaroslav, see my edited formula, which addresses the issue of varying components in the time strings :) – chuff Jun 3 '13 at 17:41
You're right - formula still not quite correct, assumes there is always the characters 'wdhm' in the string. – chuff Jun 3 '13 at 17:46
Based on the question, I'd say a fair assumption - but a horrid formula that surely will only get longer to allow for 'missing letters'. I suggested a different approach partly as the technique may be easier to remember if something similar is required in the future (rather than the formula for anyone without a phenomenal memory!) – pnuts Jun 3 '13 at 17:50