From Minutes to Decimal Formatting

[Chantal4130]

Hi

If I have Minutes displayed in cell as: 321:30

How do I Format it to display as Decimal so result would be: 321.50

I have played with the formatting but can not get it to work.

Any suggestions would be great

Thanks

[AliGW]

Try this in a helper column:

=LEFT(A1,FIND(":",A1)-1)&"."&(RIGHT(A1,2)*100)/60

then hide the column in question.

[Olly]

If your 321:30 is stored as a time and formatted as [mm]:ss in cell A1...

Then to convert to a decimal, you could use

Please Login or Register  to view this content.

and format as a number.

[Chantal4130]

That worked a treat! Thanks so Much!

Problem solved - Will add to rep

[Chantal4130]

Note: I did as Per AliGW - Which worked

[AliGW]

Thanks for the rep, but I think Olly's reply is closer to what you asked for!

[AlKey]

Here is another way
Enter formula in B1 and format cell as Number

Please Login or Register  to view this content.

v	A	B
1	321:30	321.50

[Chantal4130]

Thanks All - You've really helped

[snb]

use Olly's

[Chantal4130]

Hi All

I am back again about this. Sorry. I thought it looked correct.


Using this: 2,455:13
I am trying to get the result of: 2455.219

The closet I have gotten is with AliGW opetion but its not quite there.

example: If I was to manually do it I would enter =(2455*60+13)/60 and I get the right result. But if I have 100's it would be very time consuming.

First :
Kindly provided by AliGW was

Please Login or Register  to view this content.

Result: I get 2,455.5

Secound:
Kindly provided by Olly was

Please Login or Register  to view this content.

And Format as a number

Result: #VALUE!


Third:
Kindly provided by AlKey was

Please Login or Register  to view this content.

And Format as a number

Result: #VALUE!



Any further help on this would be much appreciated.

[AlKey]

Formula works fine for me. It works with real Date/Time and with text entries
Please see attached file.

[AliGW]

If you are happy to settle for three decimal places in all, try this:

=SUBSTITUTE(LEFT(LEFT(E1,FIND(":",E1)-1)&"."&((RIGHT(E1,2)*100)/60)*100,FIND(".",LEFT(E1,FIND(":",E1)-1)&"."&((RIGHT(E1,2)*100)/60)*100)+3),",","")

The answer is not 2455.219, by the way - it's 2455.216.

[Chantal4130]

Thanks you both I will take a look.

Thanks again for your help with this.

[AliGW]

Try this:

=(SUBSTITUTE(LEFT(A1,FIND(":",A1)-1),",","")+(RIGHT(A1,2)/60))

[AlKey]

BTW, you shouldn't be using values like 2,455:13 since this is neither Time nor a valid numeric value. However, if you do insist on using them then here is a modified formula

Please Login or Register  to view this content.

Edit: removed IFERROR function

[Phuocam]

Maybe like this ...

=SUBSTITUTE(A1,",","")*24