Forum Discussion
Converting numbers in decimal to hours & minutes
How can I convert the numbers I have with 2 decimal places to hours:minutes?
17 Replies
I know this is an old thread, but I've read through it a couple of times over the past month or so while trying to find an answer to this problem.
What I've found works for me is just dividing by 24 and then formatting the calculation to the custom format [H]:MM
example:
Decimal = 52.25
and you want this returned in the format of HH:MM:SS as 52:15:00
=A1/24
and use the custom formatting on that calculation. Simple as that! hope it helps others.
- Hi all,
the suggestions do the trick but they are quite cumbersome in special mode when thinking that
one needs to go from decimal to time and back. The issue is not only of displaying things in an human way. Also the maths have to be done properly.
The fact that no negative time is possible is really restrictive.
It is incredible to me that excel is not improving (adding features or removing bugs)
This is from a while ago, but I was looking for the same thing pertaining to times given as decimals.
So 17.80 (A1) was suppose to convert to 17.48 (mm:ss). I have been using the following to calculate paces for running.
To convert 17.80
I used =((A1-17) * 60) / 100 + 17
This gives me 17.48
This means that for each cell where the minutes changed from 17 to 18 or 19, the formula needed to be adjusted where the 17 was replaced with whatever the minutes were.
The goal was to type in a race time for a 5k (3.1 mile), like 17:25, and have the pace for each mile given. So my quick, bs setup is:
A1 = [00:17:25] (as text, not as a time or number)
B1 =HOUR(A1) * 60 + MINUTE(A1) + SECOND(A1) / 60 [17.42]
C1 = B1/3.1 [5.618]
D1 = ((C1-5) * 60) / 100 + 5 [5.37]
Like I stated above, every time the "minutes" in cell C1 changes from 5 to 6 etc., then the "minutes" in the D1 formula need to be changed to match.
For the pace to be in kilometers (5k is 5 kilometers), I change C1 and D1 to be the following;
C1 = B1/5 [3.483]
D1 = ((C1-3) * 60) / 100 + 3 [3.29]
I still don't like the requirement for manually adjusting cells, but it works for what I need.
Probably a million ways to do this, most are probably easier, if someone else has a better way let me know.
-B
thank you for this. Sadly, this is how I do it too. Very manual when doing for a long list of durations (not time of day) in decimal minutes to get to minutes and seconds.
crossover250 Again, to me: it seems an unnecessary complication, given that each time one need to find the integer and fractional part of a number and excel seems to have decided a different definition of the floor and ceiling and relative functions (wrt what I have learned at high school as well as at university). This is true especially for time/date where somebody has input human readable numbers....
To convert 17.80 in A1 to 17.48, use the formula =DOLLARFR(A1, 60)
To convert it to the real time value 17:48, use the formula =A1/1440 and apply the custom format [m]:ss to the cell with the formula.
- Plus why/how somebody should arrive at the DOLLARFR function while doing calculations with time-dates values?
Let's say you have such values in D2 and down.
In E2:
=INT(D2)/24+MOD(D2,1)/14.4
Format E2 as time, then fill down.
- But the time as a decimal 3.24 would be more like 3:15 or 3:14 and not 3:24, so that doesn't work
- what does the number presently represent? is that in hours? days? If it is in days then you just need to either change the formatting to special then time then HH:MM or FORMAT( value, "HH:MM") but if it is in hours right now then just divide by 24 to get into days and then do the above so =FORMAT( A1/24, "HH:MM") or =A1/24 and change the cell formatting.
mtarler The number now just a number with 2 decimal places
- as noted above you need to do either:
=FORMAT( A1/24, "HH:MM") or =A1/24 and change the cell formatting to show time.
the first option (FORMAT(..)) will convert it to text string (i.e. you can add or do value comparisons)
the second converts the value but then you need to go to HOME -> Cell Format go to more and then in the pop up select time and then the time format you want.
