Date Format Help

  • Thread starter Thread starter Dave \IT\
  • Start date Start date
D

Dave \IT\

I have a field where I want the display to be in text, but when I sort, I
want to sort by date. Here's my dilema, though. I sometimes need to report
specific dates (Jan 13), but other times, just the month (Jan). Then, on
the sort, I want it to return them grouped together.

Currently I have a custom date format of 'mmm dd' so my display is 'Jan 13'.
When I just type in 'Jan', it automatically changes it to 'Jan 01' to match
my format...don't want that.

So, how can I get a format that will display 'Jan 13' or 'Jan' if it's just
a month (but still let me sort on that column so all the Jan's are together,
all the Feb's are together, etc.)

Thanks in Advance,
Dave
 
You need to use a helper column that identifies the numeric value of the
month

=Month(B3) in C3 for example.

Then sort on the column with this formula.

I am not sure how a format would decide when it is appropriate to display
Jan 01 vice Jan. In any event, conditional formatting in a format string is
very limited - it is really restricted to magnitude as a decision criteria.

Conditional formatting under the format menu does not control the number
format.
 
As far as I'm aware when you format a date eg 13-jan-2003
to dd-mmm, excel forgets or loses the year and replaces it
with the current system year (though not sure why yours is
2001).

I would say you could have a variable which is the full
date and reference that when sorting.
and possibly use the format or datepart functions to
display the other.

HTH
 
Formatting after data is stored in a cell does not change the current value
stored in a cell.
 
Back
Top