SALinfo
asked on
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
=A1*24*60
jppinto
GrahamSkan, I think robhenson was giving two options. One for hours and one for minutes.
ASKER
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.
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.
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
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
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
Cheers
Rob H
ASKER
robhenson:
i looked at the above formula which is really helpful, but i did other way of achieving it:
=(MINUTE(A1)+(SECOND(A1)/6
Thanks very much for your help....
i looked at the above formula which is really helpful, but i did other way of achieving it:
=(MINUTE(A1)+(SECOND(A1)/6
Thanks very much for your help....
I maintain that all points were for Robhenson. My contribution was "Not for points" as specifically stated in my comment.
I am not exactly generous. More of fair. When it comes to my points being hijacked I am kind of stingy :-)
Many thanks to all for the reallocation of points.
Cheers
Rob H
Cheers
Rob H
Either multiply by 24 and then 60, or by 1440 alone.