converting hours and minutes to hours and decimals

[robertge]

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

 

[venkat1926]

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]

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]

Hi and thank you for your help.

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

Problem solved.