How to format cells

[Guest]

I would like to format a cell for am/pm without having to use a space in the
middle.
ex 12:00 AM

I would like to type in it as
ex 12:00AM

 

[Guest]

I looked into the link you sent me but wouldnt that refer my cells to
military time? I am looking for a way the cells can refer AM/PM

 

[Fred Smith]

If you want an easy way to type in times, use 24-hour format. Then you never
need the am/pm. You can still display it in 12-hour format. If that's not
acceptable, the next best is that you don't have to enter an 'am'. You do have
to enter 'pm' (with a space).

 

[Guest]

you can type 12:00AM Excel will treat as text not a real time

 

[Guest]

Thankyou,

If you want an easy way to type in times, use 24-hour format. Then you never
need the am/pm. You can still display it in 12-hour format. If that's not
acceptable, the next best is that you don't have to enter an 'am'. You do have
to enter 'pm' (with a space).

 

[Guest]

Do you think there may be a way to format the cell allowing the user to put
in 12:00AM and automatically converting the cell to read 12:00 AM?

 

[David Biddulph]

With a helper column you could use the formula
=IF(RIGHT(A1,2)="AM",MOD(LEFT(A1,LEN(A1)-2),0.5),IF(RIGHT(A1,2)="PM",MOD(LEFT(A1,LEN(A1)-2),0.5)+0.5,"invalidsuffix"))You could allow already valid time formats by=IF(ISNUMBER(A1),A1,IF(RIGHT(A1,2)="AM",MOD(LEFT(A1,LEN(A1)-2),0.5),IF(RIGHT(A1,2)="PM",MOD(LEFT(A1,LEN(A1)-2),0.5)+0.5,"invalid suffix")))--David Biddulph"Chris Cornell" <[email protected]> wrote in messagenews:[email protected]...> Do you think there may be a way to format the cell allowing the user toput> in 12:00AM and automatically converting the cell to read 12:00 AM?> "Fred Smith" wrote:>>> If you want an easy way to type in times, use 24-hour format. Then younever>> need the am/pm. You can still display it in 12-hour format. If that's not>> acceptable, the next best is that you don't have to enter an 'am'. You dohave>> to enter 'pm' (with a space).>>>> -->> Regards,>> Fred>>>>>> "Chris Cornell" <Chris (e-mail address removed)> wrote inmessage>> >I would like to format a cell for am/pm without having to use a space inthe>> > middle.>> > ex 12:00 AM>> >>> > I would like to type in it as>> > ex 12:00AM>> >>> >>>>>>>