Entering minutes and seconds

B

burrowsybobs

Can anyone please help before I go completely mad.

I am trying to enter minutes and seconds into a spreadsheet but even
though the cell is formatted as m:ss it still requires the user to type
in 0:01:10 for one minute ten seconds and I want to just type 1:10 for
one minute ten seconds, or just 45 for 45 seconds.

Please help.

Thanks.
 
B

Barb R.

You can format the cell(s) as mm:ss. I believe you'll still have to enter
0:45 to get 45 seconds. You won't need to enter 0:00:45.

Come back if you have more questions.

Barb Reinhardt
 
R

Ron Rosenfeld

Can anyone please help before I go completely mad.

I am trying to enter minutes and seconds into a spreadsheet but even
though the cell is formatted as m:ss it still requires the user to type
in 0:01:10 for one minute ten seconds and I want to just type 1:10 for
one minute ten seconds, or just 45 for 45 seconds.

Please help.

Thanks.
Click to expand...

Most likely you will need a VB solution, or a formula in an adjacent column.

Given the above, I assume you will never be typing in hours.

How do you want to differentiate 45 minutes, from 45 seconds?


--ron
 
B

burrowsybobs

Thanks Ron, I would like it to appear as 45.0 if it is minutes and 0.4
if it is seconds.

Regards, Juli
 
B

burrowsybobs

No that doesn't work unfortunately as that is what I thought would wor
originally and had already formatted the cells to mm:ss. If I typ
0:45, it displays 0:45 in the cell, but actually accepts that as 4
minutes and appears as 00:45:00 in the formula bar. If I type 1:10 i
accepts that as one hour and ten mins (01:10:00 in formula bar), bu
displays it as 10:00 which in my spreadsheet would look like 1
minutes.

Regards, Juli
 
R

Ron Rosenfeld

No that doesn't work unfortunately as that is what I thought would work
originally and had already formatted the cells to mm:ss. If I type
0:45, it displays 0:45 in the cell, but actually accepts that as 45
minutes and appears as 00:45:00 in the formula bar. If I type 1:10 it
accepts that as one hour and ten mins (01:10:00 in formula bar), but
displays it as 10:00 which in my spreadsheet would look like 10
minutes.

Regards, Julie
Click to expand...


Now you've got me confused. So far you have written the following
specifications:

----------------------
I want to just type 1:10 for one minute ten seconds, or just 45 for 45 seconds.

I would like it to appear as 45.0 if it is minutes and 0.45
if it is seconds.
-----------------------


1. FORGET about how the cell is formatted for now. The cell formatting ONLY
affects how Excel *DISPLAYS* the data in the cell. Cell formatting has *NO*
affect on how Excel interprets what you type into that cell.

2. How input is parsed is determined by your Regional Settings (Windows
Control Panel for a Windows machine).

3. Time is stored as fractions of a day.

4. In entering data, what do you want to type in to represent:

forty five seconds
forty five minutes
forty five minutes and forty five seconds
four hours
four hours forty five minutes forty five seconds

5. How do you want to display the above data?



--ron
 
B

burrowsybobs

4. In entering data, what do you want to type in to represent:

forty five seconds
.45

forty five minutes
45

forty five minutes and forty five seconds
45.45

four hours
four hours forty five minutes forty five seconds
nothing will be in hours


5. How do you want to display the above data?

forty five seconds
0.45

forty five minutes
45

forty five minutes and forty five seconds
45.45

Hopefully that all makes some sense.

Regards, Juli
 
R

Ron Rosenfeld

forty five seconds
45

forty five minutes
45
Click to expand...

One method I know of that will enable you to type exactly the same thing for
seconds and minutes, and have it understood properly, is to allocate one column
for minutes and the other for seconds.

So, given your specifications, you should allocate, let us say, Column A for
minutes and Col B for seconds.

In col A you can type either 45 for forty five minutes, or 45.45 for forty five
minutes and forty five seconds.

But whenever you want to type 45 and have it mean 45 seconds, you must type it
into column B.

Note also that if, in column A, you are typing a number of seconds less than
10, you must put in the leading zero. So "four minutes seven seconds" must be
entered as 4.07 and not as 4.7. Or you could enter the 4 in column A and the 7
in column B.

In column C enter the formula:

=(INT(A1)*60+MOD(A1,1)*100+B1)/86400

Format column C as: [m].ss

The values will be stored as Excel times (fractions of a day) and can be
manipulated as you would other times in Excel.

Hope this helps


--ron
 
Top