Converting time format to mins in decimal format

Hi all,
I struggling with Excel functions and its cell format conversions. i have got the time format in Excel cells as HH:MM:SS and i need this to be converted into Decimal format. lets say, i have got the time as 00:4:34 and the decimal format for this would be 4.54 (appx). so i need the conversion this way as i have got nearly 1000s of cells to do a conversion like and then multiply this column cells with a fixed number to show the results in another column of cells respectively.
SALinfoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rob HensonFinance AnalystCommented:
Excel stores time as a decimal portion of a day.  See Help for further details. To convert a time shown as hh:mm:ss mulitply that cell by:

24  for hours (number of hours in a day)
1440  for minutes (number of minutes in 24 hours)

Then format the new cell as a number format and you will see as required.

Cheers
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GrahamSkanRetiredCommented:
I think that is double accounting.

Either multiply by 24 and then 60, or by 1440 alone.
0
jppintoCommented:
Supose you have your time value on cell A1. Just use this formula say on cell A2 and format the cell as number:

=A1*24*60

jppinto
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Saqib Husain, SyedEngineerCommented:
GrahamSkan, I think robhenson was giving two options. One for hours and one for minutes.
0
SALinfoAuthor Commented:
robhenson:
I have tried this option already, but its not doing for the HH:MM:ss time format numbers that i have got, looked at excel help files, but nothing seems matching to my requirements.
So could you please detail out any formula with atleast one working example, that would be much appreciated. I am really tired of trying and searching different options, but will try further in the mean time.
0
Saqib Husain, SyedEngineerCommented:
Not for points. Just supporting Robhenson's comment

Saqib
Time-number.xls
0
Rob HensonFinance AnalystCommented:
ssabqih, It was indeed two options; thank you for clarifying.

Is your data actually numbers or is it text??

if its text it will need converting to a number first. The VALUE function should recognise the format hh:mm:ss when converting to a number. Having said that I have just tried multiplying a text time value and it didn't need the conversion, so not sure why yours is not working???

Thanks
Rob H
0
Rob HensonFinance AnalystCommented:
Have you applied the number formatting to the result. If previously empty, the cell may have been automatically formatted to time as well.

Cheers
Rob H
0
SALinfoAuthor Commented:
robhenson:
i looked at the above formula which is really helpful, but i did other way of achieving it:
=(MINUTE(A1)+(SECOND(A1)/60)). but after looking at yours i found yours is simple than this.

Thanks very much for your help....
0
Saqib Husain, SyedEngineerCommented:
I maintain that all points were for Robhenson. My contribution was "Not for points" as specifically stated in my comment.
0
Saqib Husain, SyedEngineerCommented:
I am not exactly generous. More of fair. When it comes to my points being hijacked I am kind of stingy :-)
0
Rob HensonFinance AnalystCommented:
Many thanks to all for the reallocation of points.

Cheers
Rob H
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.