Time entered (and handled as data) in simple minutes and seconds.

[MDriggers]

Using Excel 2003.

How can I get Excel to display and handle entries for duration in minutes
and seconds only (without having to enter a 4'30" entry as 0:04:30)? I have
gotten as far as getting it to display as "04:30.0", but all I need is
single digit minutes (data on this spreadsheet will never go over 9:59) and
double digit seconds (and to be able to enter them simply as in "4:30"). I
couldn't find that as an option in the ones listed for time in the cell
format dialogue box. Right now I have to enter 0:04:30 in order to get it
to work right. Is there a simpler option?

Thanks.

 

[Dave Peterson]

Maybe you could use a helper cell:
=a1/60
(formatted the way you want)

Then do all your data entry and then use that formula to convert to minutes.
Then delete the original column?

Or do all your data entry.
Put 60 in an empty cell
edit|copy that cell
select the range to fix
edit|paste special|check divide

And format the way you want.

 

[MDriggers]

Bottom posted (see below)

Maybe you could use a helper cell:
=a1/60
(formatted the way you want)

Then do all your data entry and then use that formula to convert to
minutes.
Then delete the original column?

Or do all your data entry.
Put 60 in an empty cell
edit|copy that cell
select the range to fix
edit|paste special|check divide

And format the way you want.

Dave:

Thanks for the reply. You are talking a bit over my head. I think I
understand what you're saying, but I'm not sure it will make things much
simpler. This spreadsheet is actually as much a database as anything ...
songs we use in contemporary worship service with fields for title,
composer/arranger, tempo/energy, length (in minutes and seconds rounded to
the nearest 15), then a column for each Sunday where we note when songs were
used and in what order (special number code for that).

Mo.

 

[Dave Peterson]

You have a choice.

You can enter the time correctly (hh:mm:ss).
You can enter it incorrectly and fix it later (with a formula or that
edit|paste special technique

Or you could use code that does the division for you while you're entering data.

If you think you want to try a macro, post back.

=====
Depending on what you're doing, you have another option. Just enter it as hh:mm
and treat it as mm:ss when you're looking at it.

If you have formulas that depend on those cell values, they'd probably have to
change, though.

 

[Mark San Gil]

RE: From 0:00:00 to 00:00

First, enter the minutes and seconds in the selected field and type 0:00:00. Next, hover and right click over the dataa field and select the 'Format Cells' tool; select the tab named 'Number'; choose custom in the 'Category' list; and choose mm:ss in the 'Type' list. This will display the minutes and seconds - no more, no less.

Good luck.



EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com