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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MDriggers - 14 Feb 2007 22:02 GMT

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.

Reply to this Message

Dave Peterson - 14 Feb 2007 23:27 GMT

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.

> Using Excel 2003.
>
[quoted text clipped - 8 lines]
>
> Thanks.

Signature

Dave Peterson

Reply to this Message

MDriggers - 15 Feb 2007 00:09 GMT

Bottom posted (see below)

> Maybe you could use a helper cell:
> =a1/60
[quoted text clipped - 11 lines]
>
> 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.

>> Using Excel 2003.
>>
[quoted text clipped - 12 lines]
>>
>> Thanks.

Reply to this Message

Dave Peterson - 15 Feb 2007 01:52 GMT

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.

> Bottom posted (see below)
>
[quoted text clipped - 46 lines]
> >
> > Dave Peterson

Signature

Dave Peterson

Reply to this Message

Mark San Gil - 01 Mar 2007 06:33 GMT

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

Reply to this Message