# converting hours and minutes to hours and decimals

#### robertge

##### New Member

Having to often convert tables expressed in hours and minutes to hours and decimals I would seek guidance if there is an easy way around this.

The table is imported into Excel as Text. The value might be 123:42 in column A. This needs to be left as Text as applying Time formating converts it to a value far removed from the original.

I do the following manually - as the text whole number (before the : ) varies in the number of characters I can't extract it using a text function that I know of eg RIGHT, MID or LEFT.

I re key the whole number into column B (123) then in C the minutes (42) . In D =C/60, in E =B+D.

Then copy and paste special to F to give me a new value to carry forward into other workbooks.

It is long winded but it works

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

#### venkat1926

##### Well-known Member
format the cell having time as number and multiply that by 24

e.g. enter 10:15
if you format it as number it becomes 0.43
mutiply by 24 gives 10.25
doe this hellp?
that is because excel converts the time into fraction of the day
so even if you ener 10:15 excel stores as 0.43
venkat

#### pgc01

##### MrExcel MVP
Hi robertge
Welcome to the board

You could use the text functions you know, if you would use them together with the function FIND() to get the position of the ":".

However, in this case, you don't need to do it since the text you have is in a text format.

With your text (like 123:42) in A1, try in B1

=A1*24

Remark. You may have to format B1 as number.

Hope this helps
PGC

#### robertge

##### New Member
Hi and thank you for your help.

123:42 in A1 * 24 in B1 formating B1 as number works a treat.

Problem solved.

Replies
3
Views
686
Replies
3
Views
527
Replies
6
Views
739
Replies
2
Views
116
Replies
4
Views
109

1,136,515
Messages
5,676,302
Members
419,619
Latest member
jalme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back