Times before noon show different

[xcel__]

Hi

Column A has cells with times before 12:00 noon. 10:30 in the cell shows as
10:30 in the formula bar

after 12:00 noon, shows as 01:30 in the cell. In the formula bar it shows as
01:30:00

How can I make before noon times to show as 10:30:00

This is so I can sort by times.Morning first, then afternoon and then night

Thanks inadvance

Dave

 

[Gord Dibben]

If A1 is 10:30 in cell and formula bar, that would indicate you have text, not a
real time.

If a real time in A1, formula bar should read 10:30:00 AM or PM

Second scenario also sounds like text.

Real time of 01:30 in a cell would be cutom formatted and the formula bar would
read 1:30:00 AM

The formula bar would never have 01:30:00 for any real time.

Test these times by entering in B1 =ISNUMBER(A1) then copy down.

TRUE or FALSE


Gord Dibben MS Excel MVP

 

[joeu2004]

If A1 is 10:30 in cell and formula bar, that would indicate you
have text, not a real time.

Second scenario also sounds like text.

I suspect you are correct about the cell containing text at least in
the first case.

But in the second example, how could text show "as 01:30 in the cell.
In the formula bar it shows as 01:30:00"?

My suspicion: Dave is being sloppy in his description.

I suspect the cell is formatted as Custom hh:mm. In Dave's first
example, the time is entered as text, as you conjecture. But in the
second example, the time is entered as numeric, and Dave neglected to
say that PM appears in the Formula Bar.

 

[joeu2004]

Column A has cells with times before 12:00 noon.  10:30 in the
cell shows as 10:30 in the formula bar

after 12:00 noon, shows as 01:30 in the cell. In the formula bar
it shows as 01:30:00

How can I make before noon times to show as 10:30:00
This is so I can sort by times.Morning first, then afternoon and then night

As Gord conjectures, the problem in the first example is that the time
is entered as text, not numeric.

It would behoove you to determine how that happens and avoid it if you
can.

The corrective action depends on details that you omitted.

Assuming that you have a mix of text and numeric times in A1:A100, you
can convert them all to numeric times by doing the following.

1. Select A1:A100.

2. Use Text To Columns to convert A1:A100 to numeric. In XL2003,
click on Data > Text To Columns. Be sure that Fixed Width and General
are selected at the appropriate steps.

3. Format A1:A100 as Custom hh:mm or hh:mm:ss, depending on your
requirements, which are ambiguous.

Note: Don't bother about how it appears in the Formula Bar. Focus on
how you want it displayed in the cell. The Formula Bar has its own
format, which you cannot control.

 

[Gord Dibben]

That crossed my mind........no AM or PM mention but is there.


Gord

 

[xcel__]

Hi Gord and Joe

I did intend to mention that all the cells are formatted as Custom hh:mm
and that I am also using Excel 2007.

I do apologize.

There is no AM or PM in the formula bar.

Gord the test showed all cells that had a 12 in (12:10etc) was FALSE all
others TRUE.

I've followed Joe's instructions to format the cells, all is seems ok there.

What I need to know now is how do I sort by these times?

Sorting by smallest to largest sorts has:

01:40
02:10
03:50
12:30

Sorting by Largest to smallest sorts has:

12:30
03:50
02:10
01:40

Is there a way I could sort as:

AM

11:10
12:30
01:40
02:10
03:50
08:30
09:30

PM

Hope I've been clear.....

Or am I missing something.

Your help is much appreciated.

Thanks

Dave

Column A has cells with times before 12:00 noon. 10:30 in the
cell shows as 10:30 in the formula bar

after 12:00 noon, shows as 01:30 in the cell. In the formula bar
it shows as 01:30:00

How can I make before noon times to show as 10:30:00
This is so I can sort by times.Morning first, then afternoon and then
night

As Gord conjectures, the problem in the first example is that the time
is entered as text, not numeric.

It would behoove you to determine how that happens and avoid it if you
can.

The corrective action depends on details that you omitted.

Assuming that you have a mix of text and numeric times in A1:A100, you
can convert them all to numeric times by doing the following.

1. Select A1:A100.

2. Use Text To Columns to convert A1:A100 to numeric. In XL2003,
click on Data > Text To Columns. Be sure that Fixed Width and General
are selected at the appropriate steps.

3. Format A1:A100 as Custom hh:mm or hh:mm:ss, depending on your
requirements, which are ambiguous.

Note: Don't bother about how it appears in the Formula Bar. Focus on
how you want it displayed in the cell. The Formula Bar has its own
format, which you cannot control.

 

[xcel__]

All ok

Sorted

Thanks Guys

Hi Gord and Joe

I did intend to mention that all the cells are formatted as Custom hh:mm
and that I am also using Excel 2007.

I do apologize.

There is no AM or PM in the formula bar.

Gord the test showed all cells that had a 12 in (12:10etc) was FALSE all
others TRUE.

I've followed Joe's instructions to format the cells, all is seems ok
there.

What I need to know now is how do I sort by these times?

Sorting by smallest to largest sorts has:

01:40
02:10
03:50
12:30

Sorting by Largest to smallest sorts has:

12:30
03:50
02:10
01:40

Is there a way I could sort as:

AM

11:10
12:30
01:40
02:10
03:50
08:30
09:30

PM

Hope I've been clear.....

Or am I missing something.

Your help is much appreciated.

Thanks

Dave








As Gord conjectures, the problem in the first example is that the time
is entered as text, not numeric.

It would behoove you to determine how that happens and avoid it if you
can.

The corrective action depends on details that you omitted.

Assuming that you have a mix of text and numeric times in A1:A100, you
can convert them all to numeric times by doing the following.

1. Select A1:A100.

2. Use Text To Columns to convert A1:A100 to numeric. In XL2003,
click on Data > Text To Columns. Be sure that Fixed Width and General
are selected at the appropriate steps.

3. Format A1:A100 as Custom hh:mm or hh:mm:ss, depending on your
requirements, which are ambiguous.

Note: Don't bother about how it appears in the Formula Bar. Focus on
how you want it displayed in the cell. The Formula Bar has its own
format, which you cannot control.