Excel Hours Mins
July 21, 2006 7:12 AM Subscribe
Excel Filter: I need to convert a wallop load of cells that are in decimal format to hours & minutes format. Please. More Inside.
I first thought this was a really obvious question, but its hot outside and i'm stumped. So, i've lots of cells with decimal data, but i need them in hrs and mins. So cell A with 2.30 in it, should be converted to Cell B with 2.50 in it. And so on for 2.25, 7.48, etc etc.
More importantly, i really need for this to be done with one formula.
I first thought this was a really obvious question, but its hot outside and i'm stumped. So, i've lots of cells with decimal data, but i need them in hrs and mins. So cell A with 2.30 in it, should be converted to Cell B with 2.50 in it. And so on for 2.25, 7.48, etc etc.
More importantly, i really need for this to be done with one formula.
Response by poster: sorry, let me clarify:
i have this:
data in cell A1: 2.30
which means 2 hrs and 30 minutes.
but in excel when i add 2.30 and 2.30 i get 4.60, when what i really need is 5hrs and 0 minutes.
I still have a nagging feeling that this is a really dumb question though...
posted by kev23f at 7:30 AM on July 21, 2006
i have this:
data in cell A1: 2.30
which means 2 hrs and 30 minutes.
but in excel when i add 2.30 and 2.30 i get 4.60, when what i really need is 5hrs and 0 minutes.
I still have a nagging feeling that this is a really dumb question though...
posted by kev23f at 7:30 AM on July 21, 2006
Best answer: If you really want it to be in time, not in decimal, then a slight modification on Leon's formula:
=((INT(A1)) + ((A1 - INT(A1)) / 60) *100)/24
And set the cell format to "time". The result will be that 2.30 is converted to 02:30. After that, you can add and subtract to your heart's content, and it will add like time, not a decimal (that is, 02:30 plus 02:30 equals 05:00)
posted by Bugbread at 7:47 AM on July 21, 2006
=((INT(A1)) + ((A1 - INT(A1)) / 60) *100)/24
And set the cell format to "time". The result will be that 2.30 is converted to 02:30. After that, you can add and subtract to your heart's content, and it will add like time, not a decimal (that is, 02:30 plus 02:30 equals 05:00)
posted by Bugbread at 7:47 AM on July 21, 2006
use a colon to separate your numbers. 2:30 & 2:30
The problem with that is that Excel treats them as times (half past two) and so if the result of the calculation is above 24 it flips over to 00:00 (e.g. 12:00 + 13:30 = 01:30, whereas I think the OP wanted that to equal 25:30).
posted by EndsOfInvention at 8:15 AM on July 21, 2006
The problem with that is that Excel treats them as times (half past two) and so if the result of the calculation is above 24 it flips over to 00:00 (e.g. 12:00 + 13:30 = 01:30, whereas I think the OP wanted that to equal 25:30).
posted by EndsOfInvention at 8:15 AM on July 21, 2006
I don't think you want your answers in decimal format. It's fine to convert 2.5 to 2.30, but that's still a decimal and won't add right because while hours (duration, not o'clock) add up in a regular base-10 way, minutes only go up to 60, not 100 before flipping. Excel doesn't think that way.
I think you have to use separate columns for hours and minutes, sum them separately, convert the minutes sum to hours + remainder minutes, add those hours to the hour sum, and list the remainder minutes as the final minutes.
So if you summed the following
2h 20m
3h 30m
4h 40m
You'd get a total of 9 hours in the hour column and 90 minutes in the minutes column. Through simple formulas you could convert the minutes to hours+remainder, separate that, add the hours to the hours column and display the remainder as minutes.
I'm sure there's a cleaner way, but I've never known how to do a formula for it, so I've always done it manually. I separate my decimals into two columns using the Data > Text-to-columns command with the decimal point as the delimeter. The hours column is now fine. Put a sum formula at the bottom.
Then I'd convert the data in the minutes column to minutes by making a new column next to it with a formula multiplying a cell value times 60 and dividing by 100 to come up with minutes values. So 50 would become 30, for example. Use Fill Down to do it to the whole column. Then sum that column.
You've now got total hours and total minutes and just need to convert all those minutes to hours+remainder minutes.
So in another row down, you'd have a formula which divided the summed minutes value (90) by 60 to arrive at an hours + remainder decimal value (1.5). In another row down, you'd use a simple formula (INT-based?) to add that whole number 1 to your hours sum, for a total of 10 hours. And you'd do something similar to display the remainder of .5 as 30 min in your final minutes cell.
Now, let some math brainiac come up with a clean formula for all that! It's not so bad once you've done it manually a time or two.
I couldn't get bugbread's formula to work in my test. I wonder if it's because there's sort of a difference between duration time and "o'clock" time in terms of how Excel thinks. Sounds like you've got a column of durations, not times of day. Half past 11 is not the same concept as 11 hours and 30 minutes. O'clock hours are sort of base-12 and start over at 1 after that. But duration hours just keep stacking up. You can't have 30 o'clock but you can have something 30 hours long. I tested bugbread's formula on 2.3 and it worked. But tried it on 11.7 and it didn't. It gave me 12:10 instead of 11:42. I think it's because .7 is interpreted as 70 minutes, not 7/10 of an hour.
posted by kookoobirdz at 8:17 AM on July 21, 2006
I think you have to use separate columns for hours and minutes, sum them separately, convert the minutes sum to hours + remainder minutes, add those hours to the hour sum, and list the remainder minutes as the final minutes.
So if you summed the following
2h 20m
3h 30m
4h 40m
You'd get a total of 9 hours in the hour column and 90 minutes in the minutes column. Through simple formulas you could convert the minutes to hours+remainder, separate that, add the hours to the hours column and display the remainder as minutes.
I'm sure there's a cleaner way, but I've never known how to do a formula for it, so I've always done it manually. I separate my decimals into two columns using the Data > Text-to-columns command with the decimal point as the delimeter. The hours column is now fine. Put a sum formula at the bottom.
Then I'd convert the data in the minutes column to minutes by making a new column next to it with a formula multiplying a cell value times 60 and dividing by 100 to come up with minutes values. So 50 would become 30, for example. Use Fill Down to do it to the whole column. Then sum that column.
You've now got total hours and total minutes and just need to convert all those minutes to hours+remainder minutes.
So in another row down, you'd have a formula which divided the summed minutes value (90) by 60 to arrive at an hours + remainder decimal value (1.5). In another row down, you'd use a simple formula (INT-based?) to add that whole number 1 to your hours sum, for a total of 10 hours. And you'd do something similar to display the remainder of .5 as 30 min in your final minutes cell.
Now, let some math brainiac come up with a clean formula for all that! It's not so bad once you've done it manually a time or two.
I couldn't get bugbread's formula to work in my test. I wonder if it's because there's sort of a difference between duration time and "o'clock" time in terms of how Excel thinks. Sounds like you've got a column of durations, not times of day. Half past 11 is not the same concept as 11 hours and 30 minutes. O'clock hours are sort of base-12 and start over at 1 after that. But duration hours just keep stacking up. You can't have 30 o'clock but you can have something 30 hours long. I tested bugbread's formula on 2.3 and it worked. But tried it on 11.7 and it didn't. It gave me 12:10 instead of 11:42. I think it's because .7 is interpreted as 70 minutes, not 7/10 of an hour.
posted by kookoobirdz at 8:17 AM on July 21, 2006
Response by poster: ends of invention - you're right there, in terms of how i'd like the cells to SUM. So bugbreads answer is still good, just still looking at how to fix the totals. And kookoobirdz, i looked at that approach (text to columns) but its very labour intensive in the sheet i'm working on.
Will keep fiddling away here, thanks for all the answers so far...
posted by kev23f at 8:29 AM on July 21, 2006
Will keep fiddling away here, thanks for all the answers so far...
posted by kev23f at 8:29 AM on July 21, 2006
Best answer: I think I found your answer:
You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then
use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of
times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel
from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15
rather than 01:15.
posted by EndsOfInvention at 8:52 AM on July 21, 2006
You can add times using the =SUM worksheet function. Just enter all of your times as HH:MM:SS, and then
use SUM to add them up. You may leave off the :SS if you prefer. By default, Excel will display the sum of
times in "time-of-day" format, meaning that adding 12:30 + 12:45 will yield 01:15. You can prevent Excel
from "rolling over" at 24 hours by formatting the result cell as [h]:mm which will cause it to display 25:15
rather than 01:15.
posted by EndsOfInvention at 8:52 AM on July 21, 2006
EndsOfInvention is right. I just tested it out, and 13:00 + 13:00 = 26:00 instead of 02:00, which is what my initial suggestion gave.
posted by Bugbread at 9:59 AM on July 21, 2006
posted by Bugbread at 9:59 AM on July 21, 2006
If it helps you to understand why and how Excel handles time:
Time is stored in Excel as fraction of a day. That is, 12 hours is 1/2 of a day, or, in decimal, 0.5. An hour is 1/24th of a day, or 0.0416. A minute is 1/1440 of a day, or 0.000694, etc. By default, the time function drops anything over 1, so if you add, for example, 18 hours (0.75) plus 18 hours (0.75), you get 36 hours (1.5). The default time drops the 1, displaying only the 0.5 part, so if you add 18:00 and 18:00, you get 12:00. If you use [h]:mm, the 1 is not dropped, so 1.5 is displayed as 36:00, not 12:00.
If you understand that, you can convert true decimal (i.e. 2.5 to mean 2 hours and 30 minutes) to time by converting it into a fraction of a day, then setting the display for the cell to time.
So 2.5 hours is 2.5/24ths of a day. =A1/24 converts that into a decimal (0.104167). Change the display for the cell to "hh:mm", and that will show as "02:30". Minutes, seconds, milliseconds, and the like can all be handled the same way. 10 minutes is 10/24*60 of a day. =A1/(24*60) results in 0.00694, which, if you set the display to "hh:mm" will result in "00:10".
So what Leon's formula actually did was take your "2.30", and first determine the truncated integer (2), and add it to (the remainder (2 - 2.30 = 0.30) divided by 60 (0.30 / 60 = 0.005), multiplied by 100 (0.005 * 100 = 0.5)), which turned your 2.30 into 2.5. He then divided this by 24 (2.5/24ths of a day), giving 0.10416, which, if shown in hh:mm notation, is 02:30. Once that's done, you're out of conversion land, and can add and subtract times as needed, using [hh]:mm notation if you want to express hours without rolling over at 24:00.
Yeah, sorry, lots more information than you need to do what you're trying to do, but once you understand how the system works, working with times in the future is much less of an ordeal.
posted by Bugbread at 10:29 AM on July 21, 2006
Time is stored in Excel as fraction of a day. That is, 12 hours is 1/2 of a day, or, in decimal, 0.5. An hour is 1/24th of a day, or 0.0416. A minute is 1/1440 of a day, or 0.000694, etc. By default, the time function drops anything over 1, so if you add, for example, 18 hours (0.75) plus 18 hours (0.75), you get 36 hours (1.5). The default time drops the 1, displaying only the 0.5 part, so if you add 18:00 and 18:00, you get 12:00. If you use [h]:mm, the 1 is not dropped, so 1.5 is displayed as 36:00, not 12:00.
If you understand that, you can convert true decimal (i.e. 2.5 to mean 2 hours and 30 minutes) to time by converting it into a fraction of a day, then setting the display for the cell to time.
So 2.5 hours is 2.5/24ths of a day. =A1/24 converts that into a decimal (0.104167). Change the display for the cell to "hh:mm", and that will show as "02:30". Minutes, seconds, milliseconds, and the like can all be handled the same way. 10 minutes is 10/24*60 of a day. =A1/(24*60) results in 0.00694, which, if you set the display to "hh:mm" will result in "00:10".
So what Leon's formula actually did was take your "2.30", and first determine the truncated integer (2), and add it to (the remainder (2 - 2.30 = 0.30) divided by 60 (0.30 / 60 = 0.005), multiplied by 100 (0.005 * 100 = 0.5)), which turned your 2.30 into 2.5. He then divided this by 24 (2.5/24ths of a day), giving 0.10416, which, if shown in hh:mm notation, is 02:30. Once that's done, you're out of conversion land, and can add and subtract times as needed, using [hh]:mm notation if you want to express hours without rolling over at 24:00.
Yeah, sorry, lots more information than you need to do what you're trying to do, but once you understand how the system works, working with times in the future is much less of an ordeal.
posted by Bugbread at 10:29 AM on July 21, 2006
Response by poster: No, thats really useful bugbread thanks. This date / time stuff in excel is something i've struggled with before but never had the time to get into. Some of my earlier attempts at work arounds were horrendous trainwrecks altogether.
posted by kev23f at 10:47 AM on July 21, 2006
posted by kev23f at 10:47 AM on July 21, 2006
This isn't a "you should have Googled!" snark, but I've found that it's surprisingly easy to find the solutions to Excel problems with a Google search. I found the link I posted above on the first page of the results for "excel hours minutes adding".
I think so many people have had problems with Excel's various idiosyncrasies that there's generally at least one person who's found a workaround or solution and posted it somewhere on the web.
posted by EndsOfInvention at 10:59 AM on July 21, 2006
I think so many people have had problems with Excel's various idiosyncrasies that there's generally at least one person who's found a workaround or solution and posted it somewhere on the web.
posted by EndsOfInvention at 10:59 AM on July 21, 2006
This thread is closed to new comments.
posted by EndsOfInvention at 7:21 AM on July 21, 2006