Trouble with time?
Today I want to discuss a seemengly simple problem, that was asked by onwe of my coworker.
This guy called me in a despair saying that he needs to convert time…
I was like O.K. Calm down and tell me. What format do you need to convert to what format?
It took us a couple of try to get rid of the misumderstanding abut the time formats, but we got to an agreement of what needs doing.
He had some trouble with time.
His issue was so simple around time formats yet so hard, that I couldn’t get my head around it.
All he wanted is when someone enters
13:45 in to one of his spreadsheet it would somehow convert to 13.75 as a decimal number.
As of 13 hours and .75 of an hour.
Seems doable right?
My first initial reaction was to go to my usual formulas like the INT. This bad boy will get the first part of the time from the TIME formula, then maybe a little MOD formula will get me the remainder, multiplied by 24 etc…
Oh boy, I was stupid!
I am laughing now! but for me it was a complete facepalm moment.
I totally overcomplicated my thinking!
When you read the solution below you will laugh at me!
The solution to make time to decimal in excel.
It is so simple you will not believe it!
All you have to do is to get your number format of 13:45 then simply multiply it by 24!
If you think about it. Excel give us the time as a fraction of 1. So it is literally dividing it by 24.
So to get back to a decimal number that is not a fraction of 1 all we have to do is multiply back by 24 and change the result cell format to general. This was the 45 mins will become 0.75, a 30 minute will become 0.5 and so on.
Check it below.
Would this work backwards?
Could we transform decimal to time?
All we have to do is to reverse out calculation.
So instead of multiplying by 24 we will grab the decimal and divide it by 24!
Then all we have to do is to convert the result to excels trusty time format.
We hope you liked this post and this quick two minutes tutorial helped you. Keep practicing!
Come and visit us.
Come and visit excelangel on Facebook to find and join the growing community of true office workers.
Oh, I almost forgot.
Drop us a like while you there…