Sorting dates

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Old Keith - 25 Jul 2007 02:28 GMT

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 ?

Reply to this Message

Roger Govier - 25 Jul 2007 02:48 GMT

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

Signature

Regards
Roger Govier

>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 ?

Reply to this Message

Old Keith - 25 Jul 2007 07:18 GMT

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.

> Hi
>
[quoted text clipped - 9 lines]
> > I need to extract from that list all the Fridays.
> > Is it possible to sort the list into the days of the week ?

Reply to this Message

Roger Govier - 25 Jul 2007 09:35 GMT

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.

Signature

Regards
Roger Govier

> Thanks Roger but it doesn't work if the date is prefixed with the day
> name.At
[quoted text clipped - 13 lines]
>> > I need to extract from that list all the Fridays.
>> > Is it possible to sort the list into the days of the week ?

Reply to this Message

Old Keith - 25 Jul 2007 23:56 GMT

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

> Hi Keith
>
[quoted text clipped - 32 lines]
> >> > I need to extract from that list all the Fridays.
> >> > Is it possible to sort the list into the days of the week ?

Reply to this Message

Teethless mama - 25 Jul 2007 03:08 GMT

"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

> 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 ?

Reply to this Message

Old Keith - 26 Jul 2007 00:02 GMT

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

> "date" is a defined name range of dates
>
[quoted text clipped - 7 lines]
> > I need to extract from that list all the Fridays.
> > Is it possible to sort the list into the days of the week ?

Reply to this Message