I’m in the process of creating a spreadsheet to calculate all the material I am currently able to perform, so that I can create effective set lists by considering things like the time of each piece, the key it’s in, the tempo, etc, and to have a quick reference of how long each set is without doing a bunch of math in my head.
The problem I am having is in calculating sum totals of time in minutes and seconds. I would like to be able to C&P song’s into a different part of the workbook that I could then print as a set list, and I’d like to include the total set time at the bottom.
However, when I format the column or range of cells to Time, I don’t seem to have an option for minutes and seconds (just for things like 4:00 meaning 4 o’clock, or things involving dates,) and when I do a sum total of the time of the songs in a column, the numbers are way off.
I want to be able to list the song times like so: 3:45
I want to have a cell at the bottom that does something like =sum(b17:b24)
Is this a calculation issue on my part, or a formatting issue?
Any other suggestions on how to do this?
I’m using Excel in Office X for Mac on a Power Mac G4 Tower with Panther 10.3.6
Don’t format in TIME. Do the math as just plain numbers and then convert to hours minutes and seconds by a single computation at the end.
For example if your number comes out 64.233 that/s 64.23/60 hours. There will be a decimal remainder. Multiply that remainder by 60 to get minutes. That caluculation will again have a whole number of minutes and a decimal remainder. Multply the remainder by 60 to get seconds.
For example. My spreadsheet, which isn’t EXCEL has a TRUNC(x) function which returns the whole number part of a decimal fraction.
So you can have TRUNK(64…233) in a cell which will read 64.
Then have (64.233 - TRUNK(64.233)) * 60 which will return minutes.
and so on.
Some spreadsheets have both a truncate function that returns the whole number bard and a function that returns onlt the decimal part in which case it’s even simpler.
Just ignore the previous post which I must have done while drunk, even though I’m a teetotaler, and read this one.
Divide 64.233 by 60 to get 1.071. Use TRUNC(1.071) to get 1 hr.
Take 1.071 - TRUNC(1.071) to get 0.071*60 to get 4.26. Then use TRUNC(4.26) to get 4 minutes. And finally 4.26 - TRUNC(4.26) equals .26 and multiply by 60 to get 15 sec.
And as I said in the only part of the previous post that made sense, some spreadsheets have a function, maybe FRAC(X) that will return the fractional part of a decimal.
Enter the minutes and seconds (e.g. 2min 43 sec) as 2.43
(I believe this is David Simmons suggestion)
However, if you were to total up the numbers entered in this fashion, you would get an incorrect answer.
Convert each entry (maybe use a second column), into minutes.
For example 2 min 59 sec or 2.59 = 2.983333 minutes.
Then if you total all these, you will have a minute total which would be converted as easily as changing each entry into minutes.
Okay I thought someone would be me to that so I’ll add more explanation.
Make 1 column the entries for minutes and seconds.
For example column A would say 2.59 for 2 minutes and 59 seconds.
Column B would be a calculated field and read 2.98333333 (meaning minutes)
This calculation would be done as follows:
Take the integer of 2.59 which would be 2.
Then add the remainder (.59) multiplied by 5/3.
.59* 5/3 = .9833333
Result = 2.9833333 minutes
Thanks, guys. I’m not terribly excel-literate, but that doesn’t seem to be too difficult.
I was kind of hoping there might be some feature built in that would simply recognize the format as being minutes:seconds and then do all the math accordingly.
Glad I could help picker.
By the way, I’m a guitar player myself. I’ve always liked playing electric guitar (and rewiring them too). Anyway, good luck with the spreadsheet.
That is a good suggestion, provided the number formatting is set properly.
Not to be a nitpicker here, but if you have a long list of numbers to enter, isn’t it easier to enter those numbers as (for example) 2.43 instead of 0:02:43 ?
I have my computer keypad set for the money format so that entering 243 makes it become 2.43 in Excel. It also seems that Picker has no entries that are over one hour so that extra leading zero becomes a real pain. Also, inputting 2 colons per number is difficult (requires hitting the shift key twice per entry).
Well, picker now has my formula and your suggestion so it is his choice to make.
Nevertheless, good work in coming up with a second solution !!!
Ultrafilter, per the suggestion provided by CookingWithGas, there is a way to enter time as hh:mm:ss.
In Excel, choose FORMAT … CELLS … TIME then 37:30:55 (at least that’s the format example to choose in my version of Excel).
Of course I did tell CookingWithGas:*
That is a good suggestion, provided the number formatting is set properly.*
When I use the sum function to add up a list of times it comes out to zero. I then have to reformat the cell I just formatted. A small nitpick, yes, but if you didn’t know the cell needed re-formatting, you’d think Excel couldn’t handle summing hours, minutes and seconds.
My solution is valid too. I’d like to state which method I’d prefer to use but modesty prevents me.
Just use the time format 13:30 for the column. You enter minutes and seconds as say 2, then :, then 40 and it shows 2:40. The figures add exactly how you would want them to.
I think it would be a good idea if people give some hint of how much they know about the software they are advising on. I have particularly seen lots of terrible Excel advise here with people suggesting writing VB routines for existing functions.
Why would anyone believe that software as sophisticated as Excel would need workarounds for a simple time function?
Sorry that is a lame explanation. What I have done, some time long, long ago is create the format I want. Just highlight the column(s) and choose Format\Cells. Choose custom, pick one and change it to [h]:mm. This will be available whenever you want it afterwards.
don’t ask
As I stated previously I did try the “time formatting” method but when I put the summation formula into the cell, that particular cell’s formatting reverted to a number format and said zero. Not realizing I had to re-format a cell I had previously formatted, I decided to pursue another solution.
As with a great many problems, there can be more than one solution.
I do not know if you have ever had to input a substantial amount of numerical data, but let’s suppose you had to add 500 check amounts. Would you rather have the data entry be done with a shift colon for each check or would you rather have it done as if you were just using an adding machine? My solution does not require the shift colon routine whereas don’t ask’s solution does. Still, both solutions are valid.
I’m certainly not looking for an argument here. Maybe this thread will get shut down because the question has now been correctly answered - twice LOL. The fact that a cell used for summation needed to be re-formatted shows that Microsoft is a tad at fault here too. Microsoft releasing a product that has not been thoroughly debugged? I am shocked !! :eek:
That’s a design issue, not a bug, and it stems from the lack of a standard for interval data, which I mentioned earlier. You think Excel is bad for this, try doing it in Access.
Ultrafilter
Oh you know about Access too ?
That is an incredible suck-fest !!!
I learned that when you write a formula, BEFORE you close out that design box, hit an up or down arrow and only THEN does it do any error checking.
And referencing fields - damn that can be incredibly frustrating.
Can’t forget the incredibly bloated syntax for formulas either.
I’ll stop there or else they’ll have to increase my medication level.
I don’t understand the problem with cells changing format by themselves and having to be changed back. If I select the column and choose Format\Cells and use say [mm]:ss, one of the elapsed time formats, every cell retains that format even if you perform arithmetical functions.
If you have lots of entries and wish to speed up input, which doesn’t appear to be true for the OP, the most efficient way to do it is enter the time in column A without any decimals so 25 seconds is 25, 2 minutes 31 seconds is 231, 12 minutes and 1 second is 1201. In colimn B use the formula =IF(A1<100,TIMEVALUE(0&":"&0&":"&RIGHT(A1,2)),IF(A1<1000,TIMEVALUE(0&":"&LEFT(A1,1)&":"&RIGHT(A1,2)),TIMEVALUE(0&":"&LEFT(A1,2)&":"&RIGHT(A1,2)))) and format the column as [mm]:ss. Saves one keystoke per entry.