Convert a numeric date into a text "day" value 2

[SHREVEPORT]

I have a list of dates in which I'm converting via formating into a day of the week.

For example

6/26/02 is formated to equal "Wed" via ddd.

However excel still views this as the actual date 6/26/02. Because of this I cant create summary pivot tables by day of the week. I'm wanting to look at only Saturday or Sunday trends for instance.

Does anyone know of a formula that would allow me to convert this to a text format date that I will then be able to query off of.

Thanks in advance

 

[Scott02]

Here's one way we've used at work before for the very same reason. Off to the side of your spreadsheet, set up a table listing Date and Day, where Day is the text you want to return, ie. "Monday". Then name the table, name the column headings, and name the rows in the first column under Date. In the column you want to return the results, use the following formula :

=INDEX(TABLENAME,MATCH(DATECELL,ROWRANGENAME,0),MATCH(columnheading,COLUMNHEADINGRANGE,0))

A B C D E
1 Date Day Day Activity Date
2 6/1/02 Sat =INDEX... 6/2/02
3 6/2/02 Sun =INDEX... 6/2/02
4 6/3/02 Mon =INDEX... 6/1/02

DATECELL REFERS TO E2 and below
TABLENAME REFERS TO A1:B4
ROWRANGENAME REFERS TO A1:A4
COLUMNHEADINGRANGE REFERS TO A1:B1

The column heading where the INDEX formula is located must be the same as the COLUMNHEADING you are MATCHING.


If any of this is unclear, let me know and I'll try to explain better.

Scott

 

[xlhelp]

=text("6/26/02","ddd&quot

HTH

Indu

 

[SHREVEPORT]

Thanks a bunch, works like a charm.