Date Format

[Guest]

How do I format a date so that when I type 8-4, it gives Aug-04, or when I
type 5-3 it gives May-03. This is month and year. I can use a work-around
and custom format mm/dd which gives me in the printed form what I want, but
when you click in the cell it will shows 8/4/2005 in the formula bar. This
is a month and year format and I want it to reflect nothing more in the
formula bar. If 8-4 is August 2004, I want it to give me 8/04 in the cell,
but something in the formula bar showing that this is month and year, i.e.
8/2004. Hope I have explained myself. If I format mm/yy, then I have to
type in a day, i.e. 8-8-4 will give me 08/04, but I don't want a day
reflected in the formula bar, neither do I want to type a day.

Connie

 

[Dave Peterson]

If you want to keep the value a real date, then you won't be able to do anything
to change what you see in the formulabar.

If you want to make the value a string, you could preformat the cell as text (or
start with a leading apostrophe) and type what you want.

 

[JE McGimpsey]

Nothing you do with formatting will change the way XL's input parser
interprets your entry (except that setting format to Text bypasses the
parser). Formatting is strictly for display.

To do what you're after would require preformatting the cells as Text
(in which case they wouldn't be interpreted as dates) or using an event
macro. Chip Pearson's Quick Date entry macro does something similar:

http://cpearson.com/excel/DateTimeEntry.htm

Note that dates in XL are stored as integer offsets from a base date, so
they always include the day.

 

[Guest]

Thank you, both. Too bad. I thought there would be a solution. I would
like to sort it according to date, but when it's text it puts 01/05 before
08/03, unless I go to the pain to create a list.

Connie

 

[Dave Peterson]

or use a helper column of cells and sort by that:

=datevalue(a1&"/2005")

would work on my USA settings.

 

[Guest]

Well, I formatted it "text" and created a custom list from the year 2002 to
2010, so that should do me for awhile. That way, it's available for any
worksheet. Thank you for your input.

Connie