March 5, 2017
Hi Guru,
is it possible to change the time format into decimal format with the help of formula ...like shown Eg. 136:12:10 into 136.12
138:12:16 into 138.12
Thanks
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
It is possible, but it would be wrong.
138.12 is in decimal system, a time format is in sexagesimal system.
Take a look at this example: 138:50, how this should look like? Like 138.5? It's simply wrong.
50 minutes corresponds to 0.8333 in decimal system, 138:50 is 138.8333 in decimal system.
From decimal system to time format: 138.5 corresponds to 138:30...
VIP
April 21, 2015
Catalin is right of course, but maybe the question is: can I 'translate' time format to normal numbers for instance to make calculations with?
The answer to that is Yes. So if that's what you want, tell us and if you need assistance we'll be glad to help with some examples.
Answers Post
VIP
Trusted Members
June 25, 2016
VIP
Trusted Members
June 25, 2016
Hi mey
Refer attachment.
I basically need to extract the total hours from your time and convert the minutes portion to decimal.
Since the hours extends past 24 hours the HOUR() cannot be used to get the total hours.
I use the DAY() to get the number of whole days from the time and then multiply by 24 hours.
I then use the HOUR() to get the number of hours in the day.
The MINUTE() gets the minute portion. I then divide by 100 to convert it to a decimal.
Then I add all of them together. I am not aware of any standard way to get what you wanted and created these formulas from scratch.
I am curious to know why your friend wanted such a conversion. What is it used for?
Sunny
1 Guest(s)