Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

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)

share|improve this answer
    
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).

share|improve this answer
    
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.

share|improve this answer
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.