Convert the data into hours in excel

[indy1982]

Hi Everyone,

I want to convert the data below into hours in excel. At the moment I am doing it manually.

Can someone help me or point me in the right direction in how I can do this in a more efficient way, reduce time and increase accuracy.

For example

1h 18m 28s would be 1.25 hours.
1d 4h 55m 16s would be 28.90 hours.

Example of Data

47m 26s
48m 46s
59m 36s
1h 2m 51s
1h 18m 28s
1h 20m 50s
1h 26m 37s
1h 27m 37s
1h 29m 48s
1h 44m 8s
1h 50m 45s
1h 52m 12s
1h 54m 59s
2h 39m 37s
5h 30m 41s
7h 20m 49s
7h 37m 49s
7h 39m 53s
8h 21m 45s
15h 32m 8s
1d 4h 55m 16s
1d 19h 33m 38s
1d 19h 55m 2s
2d 3h 24m 53s
3d 4h 35m 28s
3d 4h 54m 41s
3d 21h 1m 42s
5d 3h 55m 41s

I receive the data in a automated report. At the moment I have add four columns as below

1) Day- i use the mid function to work days


2) Day to Hours- multiply column 1 by 24

3)Hours- Manually type in hours eg, 4h 55m= 4.80 hours 19h 33m= 19.50 hours

4) Total Hours- Add together column 2 and 3. (Ignore seconds)


Any help would be much appreciated as this method is proving ineffective with the volume of data I am dealing with.

Thanks

Indy

 

[The Todal]

indy: I haven't tried to work out all the details needed to reach your final destination but you might want to see if the following suggestion helps...

Copy your data to Word. Then, using the Replace function, do the following (ignore quote marks when entering text into the Replace dialog):

Replace "d" with "^td"
Replace "h" with "^th"
Replace "m" with "^tm
Replace "s" with "^ts"
Replace " " with "^t"

The "^t" is the Tab character. When you do all these Replacements, you should wind up with data that you can copy and paste directly into Excel. Your numbers should be in columns all by themselves and the spaces and letters will be in columns by themselves. This will allow you to apply whatever functions you need to the numbers to get the result you want.

Hope this helps.

 

[indy1982]

Todal the data already comes to me in an excel format

 

[barry houdini]

Hello Indy,

Assuming you want to ignore seconds....and that all entries contain minutes but hours and days are optional

With data starting at A2 try this formula in B2 copied down

=IF(ISNUMBER(FIND("d",A2)),LEFT(A2,FIND("d",A2)-1)*24)+IF(ISNUMBER(FIND("h",A2)),MID(0&A2,MAX(1,FIND("h",0&A2)-2),2))+MID(0&A2,MAX(1,FIND("m",0&A2)-2),2)/60

 

[Bean Counter]

Hello Indy,

Assuming you want to ignore seconds....and that all entries contain minutes but hours and days are optional

With data starting at A2 try this formula in B2 copied down

=IF(ISNUMBER(FIND("d",A2)),LEFT(A2,FIND("d",A2)-1)*24)+IF(ISNUMBER(FIND("h",A2)),MID(0&A2,MAX(1,FIND("h",0&A2)-2),2))+MID(0&A2,MAX(1,FIND("m",0&A2)-2),2)/60

Nice formula! It was much more efficient than the solution I was about to post.

I have a question, though. What is the purpose of the MAX function in the hours and minutes computation? The ISNUMBER already traps whether the "h" or "m" is in the data. It seems to be redundant but I'm probably missing something.

 

[barry houdini]

What is the purpose of the MAX function in the hours and minutes computation? The ISNUMBER already traps whether the "h" or "m" is in the data. It seems to be redundant but I'm probably missing something.

No, you're not missing anything, Bean Counter, good call!

I didn't originally have 0&A2 so I was using MAX to avoid getting a zero from the FIND("h",A2)-2.....that approach didn't work but I overlooked the redundancy. Clearly then, this would be better

=IF(ISNUMBER(FIND("d",A2)),LEFT(A2,FIND("d",A2)-1)*24)+IF(ISNUMBER(FIND("h",A2)),MID(0&A2,FIND("h",0&A2)-2,2))+MID(0&A2,FIND("m",0&A2)-2,2)/60

 

[indy1982]

Hey barry and bean counter!!

Thanks alot for your help that formula worked a treat.

i really cant believe how helpful people are on this forum

thanks alot you have made my life a lot easier at work


cheers

 

[Roric]

No, you're not missing anything, Bean Counter, good call!

I didn't originally have 0&A2 so I was using MAX to avoid getting a zero from the FIND("h",A2)-2.....that approach didn't work but I overlooked the redundancy. Clearly then, this would be better

=IF(ISNUMBER(FIND("d",A2)),LEFT(A2,FIND("d",A2)-1)*24)+IF(ISNUMBER(FIND("h",A2)),MID(0&A2,FIND("h",0&A2)-2,2))+MID(0&A2,FIND("m",0&A2)-2,2)/60

Hey guys... found this in the forums. I am currently using Spiceworks which dictates our IT hours worked in the format listed above. The only problem is it is having a MAJOR issue when only hours are listed (no days, or minutes)

For example:
1h 30m calculates fine as 1.5
but
1h calculates an error. I am at wit's end and don't understand what I am doing wrong.

The error appears to be coming around IF(ISNUMBER(FIND("d",G9)) when I try to evaluate the error.

Anyhow all I am really doing is trying to calculate a particular column that has the listings of 1d 1h 10m or 3d 2h 10m etc.

Any help would be greatly appreciated guys. I am not an Excel master, and my degree is in Network/Internet Security. =(

P.S. Obviously the final calculation is in HOURs only.

 

[barry houdini]

Hello Roric, welcome to MrExcel, I think I've developed a better formula since 2008.....

Try this formula

=SUM(MID(0&G9&"000",FIND({"d","h","m"},0&G9&"xxdhm")-2,2)*{1440,60,1})/60

where your data is in G9. That should cope with any combination of d, h or m up to 99 days

 

[Roric]

Thanks so much for the prompt reply!

The trick is the DAYs are actually calculated in 8 hour intervals. I threw down your formula in hopes to correct the issue, but am still getting the same problem it looks like.

Giving me an answer of 0.083333(repeating). Which was the same problem I was getting before, seemingly the hours aren't calculating...

Thanks again for assisting me with my limited Excel Ninjitsu =P