Time to Number conversion

[MHau5]

In Excel I have a column with times (listed as 13:20, 15:35, etc). These numbers represent the amount of time a player played in a game, and it is saved as a Time format.

I am trying to get Excel to change it to (using the same numbers from above) 13.20, 15.35, and so forth.

However, I can't seem to find the proper way to do this. Whenever I try the substitute function and replace ":" with "." excel still remembers that the number is saved as a time.

Any thoughts on how to fix this short of doing it manually?

 

[MHau5]

In Excel I have a column with times (listed as 13:20, 15:35, etc). These numbers represent the amount of time a player played in a game, and it is saved as a Time format.

I am trying to get Excel to change it to (using the same numbers from above) 13.20, 15.35, and so forth.

However, I can't seem to find the proper way to do this. Whenever I try the substitute function and replace ":" with "." excel still remembers that the number is saved as a time.

Any thoughts on how to fix this short of doing it manually?

Also to clarify, if you click on the actual cells, Excel shows their true values as (hour:minute:second PM)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=DOLLARFR(A2*24,60)

 

[Rick Rothstein]

Another way...

=0+TEXT(A1,"h\.mm")

But note that Excel will try to be "helpful" and format the result as a Custom Time... just change the cell format to General and you will see the value as a real decimal number.

 

[MHau5]

These both worked, thank you!

 

[Phuoc]

Or try:

=TEXT(A1,"hmm")/100

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[MHau5]

Hi & welcome to MrExcel.
How about

=DOLLARFR(A2*24,60)

Thanks, Rick! How would you do the formula if it was this (for example):

3:58
4:43
3:36

 

[MHau5]

Thanks!

 

[MHau5]

Another way...

=0+TEXT(A1,"h\.mm")

But note that Excel will try to be "helpful" and format the result as a Custom Time... just change the cell format to General and you will see the value as a real decimal number.

Thanks Rick! How would you write the formula if the time was this?

3:59
4:43
4:26