This one had me going for a while the other day. I had a cell with a time in it, and just wanted to add 15 minutes to it and place it in the cell next to it. Anyway, the way to do this is to add (or subtract) #Minutes/1440.
For example, if cell A1 has the time 6:15 in it, and you want to add 15 minutes to it for cell A2 simply set it =A1+(15/1440).
The reason for this is that Excel (and Numbers, and.. most likely any other spreadsheet software out there) keeps track of dates, not time – so if you have a date of 6/19/2009, and in the next cell you want 6/20/2009 – you add 1. Well, there are 1440 minutes in a day, so you simply have to add a fraction of a day equal to the amount of time you want to add. So 15/1440 = 0.0104166667 days.
Jonathan, you just saved me so much grief! I do a lot of work with spreadsheets but I’d never had to do this. I’m putting together a ‘run order’ for a client event and I couldn’t believe how hard it was to get Numbers to do this. Many thanks.
Thanks for sharing that, saved me time in searching through functions, etc.
I’m trying to make a spreadsheet that adds up the running time of songs. I’m a DJ and I’m getting a volunteer radio show, so I’d like to type in my song selections and see how long the show would be. I’m new to the Mac and I’m using Numbers. Thanks.
Hey Brian. Did you figure this out? BTW, if you are using iTunes, it will tell you the length of a playlist at the bottom of the window, that might be an easier way to figure it out.
The easiest way to do this would be to enter the length of the song in in seconds, then at the bottom have a total of all the songs, in seconds. Then you can set a cell to be (#seconds/60) to find out how many minutes it would be.
Thanks Jonathan!
It’s always the formulas I think would be easy that get me…
Best Regards.
In Excel, it’s easy. Format the cells with a custom format of ‘[h]:mm’. The song lengths should be entered, for example, as ‘3:40’, i.e. using a colon rather than a decimal point. The ‘[h]’, tells Excel to add them up as hours beyond 24, which does not happen if you omit the ‘[]’.
This doesn’t solve the problem for me. Maybe there isn’t a solve. If I enter the number of seconds (4:15 = 255) and then formula to divide that by 60 it gives me 4.25 which is correct for minutes, but does give correct indication of what the seconds are. Anything new to fix this, or is there an app that will properly calculate the time?
Hi Ron, I’m not sure what exactly you are trying to accomplish here. You want to be able to enter in a number of seconds and have it placed in to one cell telling you the number of minutes/seconds? Or do you want to enter the number of seconds and have it place in to two separate fields Minutes and Seconds?
I found an easy way for adding song lengths in Numbers (I am using version 3): Click on your column header to select the entire column. Click on the Format icon to open the Format sidebar. Select Cel. Change the data format to Duration. To see the total, just do a regular SUM function for that column.
Brilliant well done, but trying to do this for last 3 days
Adding times like on a show rundown or run-of-show is actually easy, practice by trying this:
Cell A2 enter “1:00 PM”
Cell B2 enter “15m” or “0:15”
Cell C2 enter “=” (and the trick here is that the first entry of “=” only activates the formula bar) then enter “=” again indicating you are creating a formula, followed by “A2+B2”
——-the result is the addition of the two times, just like in excel. Works with time subtraction also, the key is in the double entry of the “=”.
srl
Chris, I am trying to do just that but Numbers somehow reverts back to automatic format when I deselect the cells and selected them again. Why does the format not stick to duration?
Ahhh I figured it out already… if you enter a string like “6m 30s”, Numbers recognizes this as a duration and keeps the format! And finally the sum function also works like a charm. (This should also work well for your case Brian)
Hi Jonathan I’m trying to creat a spreadsheet that would help me calculate my daily hours each week on numbers but I have no idea on how to do it. A little help will be appreciated.
I’d recommend checking out the last few comments from others here. As Sven noted above you can actually enter in a format of “6m 30s”, or probably in your case something like “2h 15m”. If you have any problems, try changing the Format of the cell(s) to Duration.