Converting hours and minutes to digital

[scarames]

I've got spreadsheets that are new every three months. Two columns are based on hours and minutes that the vehicles run in a day. It comes out of a proprietary software that prints out for instance, 158h13m, in a general format. I need to convert these columns to digital for use in another spreadsheet. Right now I'm going through cell by cell, using the formula =158+13/60, changing to a number format. If there is a way to copy this formula I don't know it. But I need to do this with two entire columns on about 30 different sheets. I don't know VBA. Is there an easier way to do this than what I'm doing now? I appreciate your help or guidance on this.

Example:
Col A Formula Col B
149h30 =149+30/60 149.50
80h25 =80+25/60 80.42
109h15 =109+15/60 109.25
8h20 =8+20/60 8.33
23h20 =23+20/60 23.33

[simarui]

how about:

Please Login or Register  to view this content.

input into b2, then you can click the bottom right corner of the cell and it will fill all the way down to the end of the data in column A

[Richard Buttrey]

Hi,

One way

Please Login or Register  to view this content.

[scarames]

Thank you so much! Checked it against my numbers done one by one and it was just the same. You have saved me hours of work. Much appreciated!

[scarames]

Richard, thanks for answering, but this one didn't come up with the /60 answer. It kept it in just numerical terms. I needed it in time, but in digital. Simarui's solution worked great for what I've got. I appreciate your answering.

[Richard Buttrey]

Richard, thanks for answering, but this one didn't come up with the /60 answer. It kept it in just numerical terms. I needed it in time, but in digital. Simarui's solution worked great for what I've got. I appreciate your answering.

Yes, good point. I'd missed that completely.

[shg]

Another way:

Row\Col	A	B	C
1	149h30	149.5	B1: =24*SUBSTITUTE(A1, "h", ":")
2	80h25	80.4
3	109h15	109.3
4	8h20	8.3
5	23h20	23.3

[Richard Buttrey]

..slight modification to shg's suggestion when the string ends in "m"

Please Login or Register  to view this content.