Forum Discussion
Converting numbers in decimal to hours & minutes
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?
- On top of that one need also the inverting function of DOLLARFR (and again without doing double deadly jumps, why should I use a spreadsheet if I have to think of formulas 100 characters long?)
- indeed the formula shows the proper value. the question is whether this makes proper maths or
not, which is the real interesting things.
I was able to 'show' human-readable times, but not to perform proper maths without too much gymnastics which does not work with negative numbers...
At the end of the day, the same math is needed to perform arithmetics on angles (degrees + minutes + seconds). I know I can always calculate first things in radiants and then back. But again 'too much effort' for something which should be directly supported.
