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 ?

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

--
Regards
Roger Govier



"Old Keith" wrote in message
...
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 ?

"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


"Old Keith" wrote:

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 ?

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.


"Roger Govier" wrote:

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

--
Regards
Roger Govier



"Old Keith" wrote in message
...
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 ?

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
FormatCellsNumberCustom 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 BPasteSpecialValues back over the formulae.
Mark column B, DataText to columnsNextNextclick Datechoose from
dropdown M/D/YFinish

Format the resulting data as shown above.
--
Regards
Roger Govier



"Old Keith" wrote in message
...
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.


"Roger Govier" wrote:

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

--
Regards
Roger Govier



"Old Keith" wrote in message
...
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 ?

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

"Roger Govier" wrote:

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
FormatCellsNumberCustom 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 BPasteSpecialValues back over the formulae.
Mark column B, DataText to columnsNextNextclick Datechoose from
dropdown M/D/YFinish

Format the resulting data as shown above.
--
Regards
Roger Govier



"Old Keith" wrote in message
...
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.


"Roger Govier" wrote:

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

--
Regards
Roger Govier



"Old Keith" wrote in message
...
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 ?

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

"Teethless mama" wrote:

"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


"Old Keith" wrote:

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 ?