Sorting dates

O

Old Keith

I have a list of dates in the format :
Wednesday,Jul 25,2007
I need to extract from that list all the Fridays.
Is it possible to sort the list into the days of the week ?
 
R

Roger Govier

Hi

With data in column A, enter in B1
=WEEKDAY(A1)
This will return the weekday number for that date and for 25/07/2007 that
will return 4.
Copy down.
Use Autofilter to select all values of 6 in column B
 
T

Teethless mama

"date" is a defined name range of dates

=IF(ISERR(SMALL(IF(WEEKDAY(date)=6,ROW(INDIRECT("1:"&ROWS(date)))),ROWS($1:1))),"",INDEX(date,SMALL(IF(WEEKDAY(date)=6,ROW(INDIRECT("1:"&ROWS(date)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down
 
O

Old Keith

Thanks Roger but it doesn't work if the date is prefixed with the day name.At
least I can't make it work on Excel 2003 or 2007.
 
R

Roger Govier

Hi Keith

Are you saying that your dates are text values, not true Excel dates?

If you enter 25/07/2007 in a cell and then use
Format>Cells>Number>Custom> dddd, mmm dd,yyyy
it will display as
Wednesday, Jul 25,2007
but it will still be held as an Excel serial date, upon which the Weekday()
function will work.

If you need to convert text values to true dates, and provided you have a
blank column to the right of your data, then one way would be
In B1 enter =MID(A2,FIND(",",A2)+1,255) and copy down as far as required.
Copy the whole of column B>PasteSpecial>Values back over the formulae.
Mark column B, Data>Text to columns>Next>Next>click Date>choose from
dropdown M/D/Y>Finish

Format the resulting data as shown above.
 
O

Old Keith

Hi Roger
I followed your advise and it worked perfectly! Thanks very much.
The problem was that the data I was dealing with was imported as a CSV file
and would not respond to Excel commands, but when I re-entered the dates as
you suggested, it fixed the problem. And that "weekday and filter" is just
magic !
regards from Old Keith
 
O

Old Keith

Thanks for your help Mama. I have yet to figure out the formula but intend to
try it.
Regards, Old Keith
 
Top