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

 

[Jaybee67]

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.

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

 

[AzamAzid]

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

Hi Barry,

I want to include the seconds as well, could you provide a clean formula (no need to convert into hours, just a standard time)

 

[Excel Girl]

barry houdini Is there a way to edit your formula to work even when the format of the data is not consistent? For example the ds are missing and the cell just contains the hs.