Date in cell keeps adding the current year.

[Excelsior731]

The date in the cell keeps adding the year when I don't want it to, even if I
select "Category - Date" and "Type - 3/14". I need to have ONLY the month and
day (2 digits each in the date format so it's sortable), not the year, but
the year keeps appearing after the date in the formula bar.

 

[FSt1]

hi
does the date appear as 4/10 in the cell? if so then everything is normal.
fomating changes the way the data looks. it does not change the data. what
appears in the formula bar is what is in the cell such as a fomula. the
formula appears in the formula bar and the results appears in the cell. in
your case, the formated version of the date would appear in the cell but the
full date would appear in the formula bar.

Regards
FSt1

 

[Mike H]

Hi,

Excel is interpreting 3/14 as a date and because you omit the year Excel
adds the current one, even when you format the cell to show months and days
the year is still there, you just cant see it.

Extract the day and month to a helper column and use that for your sort.

=TEXT(A1,"mm/dd")

Mike

 

[Excelsior731]

The date appears as 04/05 in some cells and 4/5 in others. Where it's 04/05,
it doesn't add the year in the formula bar. In the others, it removes the
zeros and the current year does appear in the formula bar:
Cell Bar
04/05 = 04/05
4/5 = 4/5/2009

What I don't understand is why there should be any difference if all cells
are formatted the same: to just show the date and month.

 

[Excelsior731]

What I'll do is convert the date to text (5/9 is now 05/09) and then convert
that back to "date," which seems to work. Except now some of my full dates
(MM/DD/YYYY) appear as "######" in the cells. Wierd.

 

[FSt1]

hi
if excel can recognize the number in the cell as a date, then it will format
to date. if not then excel will consider the number as not a number or text.
does the 04/05 "data" appear in the formula bar as '04/05. if so the
appostrophe in front is designating it as text becasue excel can't recognize
the number as a date. you may have the cell formated as date but if excel
sees text, then the formating is there but doen't take.
try this.. reformat the 04/05 date to general. if it doesn't change to 39908
, then excel is not seeing it as a number(formated as date). it's seeing it
as text and that may be why only 04/05 appears in the formula bar.

Regards
FSt1

 

[FSt1]

hi
if your full dates (MM/DD/YYYY) appear as "######", try widening your
columns.

Regards
FSt1