Certain date format in cells

  • Thread starter Thread starter Chris van Lelyveld
  • Start date Start date
C

Chris van Lelyveld

If I format a cell to 'Custom' 'dd-mmm' it displays as
e.g. 01-Jan. If, however, the cursor is on the cell, it
displays '01/01/2003' as the actual contents of the cell
in the formula bar. This becomes a problem when you are
building a list over time that includes (in this instance)
birthdays where you are not interested in the birth year.
When sorted in birthday sequence, it will obviously group
those together that were added in the same year i.e. 2002
will appear separately from 2003 etc. Any workaround?
 
Chris van Lelyveld said:
If I format a cell to 'Custom' 'dd-mmm' it displays as
e.g. 01-Jan. If, however, the cursor is on the cell, it
displays '01/01/2003' as the actual contents of the cell
in the formula bar. This becomes a problem when you are
building a list over time that includes (in this instance)
birthdays where you are not interested in the birth year.
When sorted in birthday sequence, it will obviously group
those together that were added in the same year i.e. 2002
will appear separately from 2003 etc. Any workaround?
Click to expand...

Dates in Excel are stored as serial numbers, that is the number of days
since 31st Dec 1899 (i.e. 1st Jan 1900 is 1). So they inherently contain a
year. When you format a cell containing a date, you only alter the way the
date is displayed. The format dd-mmm doesn't contain a year, so this isn't
dislayed - but it still exists as part of the value in the cell.

Just because you use the format dd-mmm doesn't mean you can ignore entering
the year. If you don't include a year when entering a date, Excel assumes
you mean the current year. For your purposes, you should be entering a
specific year with each of your dates - any one, as long as it's the same
for all entries.

Alternatively, you could use a formula in an adjacent column to calculate
dates all in the same year from your dates in various years, and then sort
on that column. For example, for a date in A1 you could use:
=DATE(1900,MONTH(A1),DAY(A1))
 
One way:

Sample:
Col_A Col_B
9/9/1943 =A1
3/8/1944 =A2
2/27/1974 =A3
9/9/1999 =A4
1/7/2003 =A5

1. Reference cell in Col_B to cell in Col_A.
2. When done, select entire column A & B.
3. Select from main menu Data|Sort and click Options
button.
4. Sort Options dialog box pops up & u select the drop-
down list below "First key sort order" and choose "Jan,
Feb, Mar, Apr, May, Jun......".

This will tell Excel to do specific sorting sequence using
month sequence as its sorting criteria. U can create your
own custom list via Tools>Option>Custom List Tab. U may
consult online help for "Create a custom fill series or
sorting order".

HTH
Edmund Seet
 
Forgot to mention that u should sort by colunm B (after
selecting both Col A & B).

Anyway, Paul's method is a better option than mine as it
caters Day sequence as well.

Good day.
 
Back
Top