• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • Last Modified:

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.
0
SALinfo
Asked:
SALinfo
  • 4
  • 4
  • 2
  • +2
1 Solution
 
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
 
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 4
  • 4
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now