How can I format elapsed time as "[mm]: ss" without getting "AM PM

[Guest]

I need to format a cell as elapsed time for a run time. For example, I want
to use "13:15" to designate "13 minutes and 15 seconds" and use it in an
ascending array (VLOOKUP). Right now, Excel makes it into "13:15:00 AM",
which doesn't help me.

 

[David Biddulph]

Enter either 0:13:15 or 13:15.0
If the values are already in the cells & you want to convert, use Edit/
Paste Special/ Divide, to divide by 60.

 

[David Biddulph]

Having read your message for a second time, I'm intrigued. I'm interested
to how 13:15:00 can be AM. I would have expected 13:15:00 or 1:15:00 PM.
What format do you see in the format box if you select "custom"? ... and
what value do you see in the cell if you format to General or Number?

 

[Guest]

You're right about the AM/PM--I was just using it as a formatting example.
In the formula block, "1:15:00 PM" appears. I've chosen "[mm]:ss" in the
format menu because it's the closest to what I'm looking for. But when I
type "13:15" in that format, "795:00" is the result, which is not what I want.
I want minutes and seconds as a QUANTITY of time that can be sorted fastest
to slowest. How can I get EXCEL to do this?
Thanks for your help.
PS: Formatted to "general" I get "0.552083333333333"; formatted to "number"
I get ".55"(2 decimal places).

 

[Guest]

One more thought:
The data isn't doing what I want it to. Here's what I've got on my worksheet
on which I'm looking up scores: 1. A column with a list (from fastest to
slowest) of run times (ie. ascending slower and slower); 2. A column (to the
right) with corresponding points from 100 (fastest) to 45 (slowest) in
increments of 5 points (see below for example):
Row'G' Row'H'
9:20 100
9:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:00 55
14:15 50
14:30 45
The correct points for the time "12:46" is 65, but my formula currently
makes the result 70.
Here's my formula (on the master page): =VLOOKUP(U4,'M4'!G2:H13,2)
Appreciate your thoughts.
NavyPianoMan

 

[Jon Peltier]

When you enter 13:15, you are entering 13 hours and 15 minutes. Enter
instead 0:13:15. This is why David suggested you divide your misbehaving
values by 60, to convert from H:MM to M:SS.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

You're right about the AM/PM--I was just using it as a formatting example.
In the formula block, "1:15:00 PM" appears. I've chosen "[mm]:ss" in the
format menu because it's the closest to what I'm looking for. But when I
type "13:15" in that format, "795:00" is the result, which is not what I
want.
I want minutes and seconds as a QUANTITY of time that can be sorted
fastest
to slowest. How can I get EXCEL to do this?
Thanks for your help.
PS: Formatted to "general" I get "0.552083333333333"; formatted to
"number"
I get ".55"(2 decimal places).

Having read your message for a second time, I'm intrigued. I'm
interested
to how 13:15:00 can be AM. I would have expected 13:15:00 or 1:15:00 PM.
What format do you see in the format box if you select "custom"? ... and
what value do you see in the cell if you format to General or Number?

 

[Guest]

David, the "divide by 60" isn't working for me--I don't see where I can put
the "60". Once I select the "divide" feature under Paste Special, and click
OK, it just results in a funky number. For example, the time "9:20" results
in "1440:00" when I apply Paste Special and then format as [mm]:ss.
Thanks for any enlightening thoughts.
NavyPianoMan

 

[David Biddulph]

1440:00 is 24 hours, which is what you get from a number of 1 in the cell,
so it sounds as if you divided the content of the cell by itself.

Put 60 in a spare cell. Select that cell and copy. Then select the range
of cells where you have your times in hours and minutes that you want
converting to minutes and seconds. Then choose Edit/ Paste Special/ Divide,
and OK. If you get a decimal number at that stage (0.00648148148148148 from
your 09:20), it's because Excel has reformatted the cell to General, so
reformat it to [m]:ss if you want to see 9:20, or [mm]:ss if you want to see
09:20.
--
David Biddulph

David, the "divide by 60" isn't working for me--I don't see where I can
put
the "60". Once I select the "divide" feature under Paste Special, and
click
OK, it just results in a funky number. For example, the time "9:20"
results
in "1440:00" when I apply Paste Special and then format as [mm]:ss.
Thanks for any enlightening thoughts.
NavyPianoMan

Enter either 0:13:15 or 13:15.0
If the values are already in the cells & you want to convert, use Edit/
Paste Special/ Divide, to divide by 60.

 

[David Biddulph]

If an Excel function doesn't give the answer you are expecting, I would
recommend looking up the formula in Excel help and seeing what it says.

In your case you've omitted the TRUE/ FALSE for the range_lookup parameter
for the VLOOKUP function, and help tells you:
"If TRUE or omitted, an approximate match is returned. In other words, if an
exact match is not found, the next largest value that is less than
lookup_value is returned."

Isn't that what it has done?

 

[Guest]

I understand how the range_lookup parameter works (I'd researched this), but
in my case, I can't use "FALSE" because it will just produce a #N/A and
"TRUE" or omission produces a mistaken result due to chosing the NEXT
approximate match. I actually need to chose the LAST approximate match. How
can I do this?
Here's my example again (this is from the worksheet 'M4'!):
Row'G' Row'H'
9:20 100
9:45 95
10:00 90
10:30 85
11:00 80
11:15 75
12:00 70
13:00 65
13:45 60
14:0 55
14:15 50
14:30 45
The correct points for the time "12:46" (U4) is 65, but my formula currently
makes the result 70.
Here's my formula (on the master page): =VLOOKUP(U4,'M4'!G2:H13,2)
Thanks,
NavyPianoMan

 

[Guest]

IT WORKED! Thanks!
--NavyPianoMan

1440:00 is 24 hours, which is what you get from a number of 1 in the cell,
so it sounds as if you divided the content of the cell by itself.

Put 60 in a spare cell. Select that cell and copy. Then select the range
of cells where you have your times in hours and minutes that you want
converting to minutes and seconds. Then choose Edit/ Paste Special/ Divide,
and OK. If you get a decimal number at that stage (0.00648148148148148 from
your 09:20), it's because Excel has reformatted the cell to General, so
reformat it to [m]:ss if you want to see 9:20, or [mm]:ss if you want to see
09:20.
--
David Biddulph

David, the "divide by 60" isn't working for me--I don't see where I can
put
the "60". Once I select the "divide" feature under Paste Special, and
click
OK, it just results in a funky number. For example, the time "9:20"
results
in "1440:00" when I apply Paste Special and then format as [mm]:ss.
Thanks for any enlightening thoughts.
NavyPianoMan

Enter either 0:13:15 or 13:15.0
If the values are already in the cells & you want to convert, use Edit/
Paste Special/ Divide, to divide by 60.
--
David Biddulph

I need to format a cell as elapsed time for a run time. For example, I
want
to use "13:15" to designate "13 minutes and 15 seconds" and use it in
an
ascending array (VLOOKUP). Right now, Excel makes it into "13:15:00
AM",
which doesn't help me.