converting hours and minutes to hours and decimals

robertge

New Member
Joined
Oct 14, 2006
Messages
3
Can anyone assist please.

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
Joined
Aug 21, 2005
Messages
4,824
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
Joined
Apr 25, 2006
Messages
19,884
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
Joined
Oct 14, 2006
Messages
3
Hi and thank you for your help.

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

Problem solved.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top