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

 

[Roric]

Correction... it does appear to be working now.

Is there a way though I can fix that day issue? I am assuming based on the answer I got with this formula that it is calculating days in 24 hour intervals, and I have it set up to do an 8 hour interval work day for 1d.

Thanks again!

 

[barry houdini]

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

In this version the 1440 is the number of minutes in a (24 hour) day. so if you want to count a day as 8 hours change that to 480, i.e.

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

 

[Roric]

In this version the 1440 is the number of minutes in a (24 hour) day. so if you want to count a day as 8 hours change that to 480, i.e.

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

You sir.... Are a genius =)

 

[Roric]

I just had to log in today from work to let you know how much your help is appreciated, Barry Houdini.

Thanks a lot for this formulation, you saved me a lot of time, and on top of it made me look good. I will recommend this site to anyone with Excel Formula Questions in the future...

Thank you again, Barry.

-Roric

 

[IonutM]

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

Hy Barry, how convert

7h 39m 53s
8h 21m 45s
15h 32m 8s
1d 4h 55m 16s
1d 19h 33m 38s .....in minute.

 

[Shoheb]

Great help!

The formula is working fine is all scenarios except when you ONLY have Hours to convert (Ex: 2h)

I have updated the formula to correct this...just wanted to share with rest of us..

=IF(ISNUMBER(FIND("h",A2)),MID(0&A2,FIND("h",0&A2)-2,2)*60)+IF(ISNUMBER(FIND("m",A2)),MID(0&A2,FIND("m",0&A2)-3,2))

***i am not considering Days and seconds

 

[littledaga]

Hello,

I'm having a similar issue, my data as seen below is aligned auto to the left and starts in Column P2

6d 18h 36m
12m
22h 19m
5h 42m
1h 37m
2m

I have tried the following formula but the get this output,

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

6d 18h 36m - outputs - 14:24:00

Do I need to change the column formatting?

Hope someone can help me, thanks in advance.

 

[Abhi306]

Thanks man great method but why cant we use this for above 99 days my data is in 200days or more can anyone tell how to use this formula for my requirement

 

[barry houdini]

Thanks man great method but why cant we use this for above 99 days my data is in 200days or more can anyone tell how to use this formula for my requirement

Hello Abhi306, welcome to MrExcel

Can you give some representative examples?

 

[Jaybee67]

I’m having the same problem- can anybody help pls?