Reformat time data into decimal and remove excess charactors from field

Hi

I have obtained the following data which I need to put into a spreadsheet to import into an accounts program. I can't change the way I get the data which is as follow

Hrs worked Rate
05:55:00 £30.00/Hour
07:40:00 £21.00/Hour
05:45:00 £30.00/Hour

What I need to have is 5.92 30.00
7.67 21.00
5.75 30.00 ie the time format in decimal and loose all the unnecessary
symbols etc for the rate

I'm sure this is probably easy for you clever people but I need help please :-)

Assuming that the hours worked are in A1, paste this into A2: =HOUR(A1)+MINUTE(A1)/60+second(A1)/60/60. Make sure that the cell is formatted as a number.

If the rate is in B1, paste this into B2: =MID(B1,3,5).

Hi Thank you for the response.

Yes they are in two separate fields. The funny A is actually what I get from the export of data from the other program! £30.00/Hour is an exact copy.

Originally Posted by AliGW

Assuming that the hours worked are in A1, paste this into A2: =HOUR(A1)+MINUTE(A1)/60+second(A1)/60/60. Make sure that the cell is formatted as a number.

If the rate is in B1, paste this into B2: =MID(B1,3,5).

Thats it You are so clever! You can't believe how much time this will save me. Thank you SO much

You are very welcome, Tracie!

Originally Posted by XOR LX

Why not just =24*A1?

Yes, that works, too.