I’m trying to make a spreadsheet adding the “run times” of a group of songs, but every time I put in a piece of data, like 4:24 (meaning the song is four minutes, 24 seconds long) the cell thinks I’m talking about 4:24 AM. So when I go to add the selected cells, it has no idea what to do with them. Anyone know what I’m doing wrong?
Try entering the time as 0:04:24.
Crap!
Format the cell as Time using the HH:MM:SS format and then enter your times as above.
Bless you.
Hmm… it works very unevenly, though. It worked in the first column (sort of; it added the minutes but not the seconds) but by row D, the formula won’t add at all.
All the cells with time in them will have to be formatted as HH:MM:SS.
Are you all set? If not, here’s an alternate approach:
Use two cells (columns), one for the actual value, and one for the display. The “actual value” cell/column should contain the integer number of seconds. So 4:24 would be entered as 264.
The display cell should contain the following formula, assuming the “actual value” cell is A1:
=INT(A1/60) & “:” & RIGHT(“0” & MOD(A1,60),2)
This won’t give you hours, but it could be tweaked to handle hours if it’s really necessary. (It’ll show 2 hours as 120 minutes.)
We had a thread about this in February.
Here are even more suggestions:
http://boards.straightdope.com/sdmb/showthread.php?t=303269&highlight=Excel
Good luck.