Sorting day names chronologically

  • Thread starter Thread starter Walter Briscoe
  • Start date Start date
W

Walter Briscoe

I use Excel 2003. I have data including a column of dates.
My example happens to be sorted alphabetically.
e.g.
Fri
Mon
Mon
Sat
Sat
Sat
Sat
Thu
Thu
Thu
Tue
Wed
Wed

I want to sort it alphabetically. i.e. to output
Mon
Mon
Tue
Wed
Wed
Thu
Thu
Thu
Fri
Sat
Sat
Sat
Sat

(My true date is more complicated. I have simplified to an example I
still can't make work. I have 3 keys with the day key being the second.)

I googled and <nhprague.blogspot.com/2012/11/sort-your-month-and-day-
names.html> seemed a fairly simple set of instructions:

1) Select any cell in the column of month or weekday data.
a) I selected A1
2) Choose Data | Sort from the menu bar to display the Sort window.
b) I did so. The Sort dialog opened with "Column A" and "No header row".
3) Click the Options button. (In 2007, choose Custom List from the Order
dropdown list.)
c) I did so. The Sort options dialog opened
4) Select the appropriate custom list using the First Key Sort Order
dropdown list.
d) I selected "Mon, Tue, Wed, Thu, Fri, Sat, Sun", left "Case sensitive"
unchecked, and left orientation at "Sort top to bottom".
5) Click OK twice to dismiss the open dialog boxes and apply the sort
order.
e) OK the first time closed the Sort options dialog, OK the second time
sorted the data alphabetically.

I feel quite stupid in my need to ask this question.
A correction to what I describe would be good.

I don't understand "You Tube", but hope a solution like that may exist.
 
Hi Walter,

Am Sat, 28 Jun 2014 13:59:18 +0100 schrieb Walter Briscoe:
(My true date is more complicated. I have simplified to an example I
still can't make work. I have 3 keys with the day key being the second.)
Click to expand...

the instructions are right but it only works correctly if the day key is
the first one
The keys are important in the order of use.


Regards
Claus B.
 
I use Excel 2003. I have data including a column of dates.
My example happens to be sorted alphabetically.
e.g.
Fri
Mon
Mon
Sat
Sat
Sat
Sat
Thu
Thu
Thu
Tue
Wed
Wed

I want to sort it alphabetically. i.e. to output
Mon
Mon
Tue
Wed
Wed
Thu
Thu
Thu
Fri
Sat
Sat
Sat
Sat

(My true date is more complicated. I have simplified to an example I
still can't make work. I have 3 keys with the day key being the
second.)

I googled and <nhprague.blogspot.com/2012/11/sort-your-month-and-day-
names.html> seemed a fairly simple set of instructions:

1) Select any cell in the column of month or weekday data.
a) I selected A1
2) Choose Data | Sort from the menu bar to display the Sort window.
b) I did so. The Sort dialog opened with "Column A" and "No header
row". 3) Click the Options button. (In 2007, choose Custom List from
the Order dropdown list.)
c) I did so. The Sort options dialog opened
4) Select the appropriate custom list using the First Key Sort Order
dropdown list.
d) I selected "Mon, Tue, Wed, Thu, Fri, Sat, Sun", left "Case
sensitive" unchecked, and left orientation at "Sort top to
bottom". 5) Click OK twice to dismiss the open dialog boxes and apply
the sort order.
e) OK the first time closed the Sort options dialog, OK the second
time sorted the data alphabetically.

I feel quite stupid in my need to ask this question.
A correction to what I describe would be good.

I don't understand "You Tube", but hope a solution like that may
exist.
Click to expand...

Have a look at the WEEKDAY() function and consider using a helper
column with the following formula...

=WEEKDAY(MyDate)

...where "MyDate" is a col-absolute, row-relative local scope defined
name that points to your date col. This helper col can be hidden!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
In message said:
I use Excel 2003. I have data including a column of dates.
My example happens to be sorted alphabetically.
e.g.
Fri [snip]
Wed
Wed

I want to sort it alphabetically. i.e. to output
Mon
Mon [snip]
Sat
Sat

(My true date is more complicated. I have simplified to an example I
still can't make work. I have 3 keys with the day key being the second.)

I googled and <nhprague.blogspot.com/2012/11/sort-your-month-and-day-
names.html> seemed a fairly simple set of instructions:

1) Select any cell in the column of month or weekday data.
a) I selected A1
2) Choose Data | Sort from the menu bar to display the Sort window.
b) I did so. The Sort dialog opened with "Column A" and "No header row".
3) Click the Options button. (In 2007, choose Custom List from the Order
dropdown list.)
c) I did so. The Sort options dialog opened
4) Select the appropriate custom list using the First Key Sort Order
dropdown list.
d) I selected "Mon, Tue, Wed, Thu, Fri, Sat, Sun", left "Case sensitive"
unchecked, and left orientation at "Sort top to bottom". 5) Click OK twice
to dismiss the open dialog boxes and apply the sort order.
e) OK the first time closed the Sort options dialog, OK the second time
sorted the data alphabetically.

I feel quite stupid in my need to ask this question.
A correction to what I describe would be good.

I don't understand "You Tube", but hope a solution like that may exist.
Click to expand...

Have a look at the WEEKDAY() function and consider using a helper column
with the following formula...

=WEEKDAY(MyDate)

..where "MyDate" is a col-absolute, row-relative local scope defined name
that points to your date col. This helper col can be hidden!
Click to expand...

Thanks, Garry.
I forgot to say that I could obviously use a helper column set by a mapping
function and sort on that.
Your use of WEEKDAY is much more economical than my use of HLOOKUP.
Well it would be if WEEKDAY took a string argument, rather than a serial
number. If A1 is "Fri" and A2 is =WEEKDAY(A1), A2 evaluates as #VALUE!
OTOH
=HLOOKUP(A1,{"Fri","Mon","Sat","Sun","Thu","Tue","Wed";5,1,6,7,4,2,3},2,TRUE)
evaluates as 5 which matches what I want.

Thanks also to Claus Busch who made the very helpful remark "it only works
correctly if the day key is the first one". In my example, the only key is
the first. but it still does not work.
With my real data, the daynames have to be in the second key column.
So I was exploring an unhelpful direction.
I still don't know why my simplified example does not work as both Claus and
I think it should.
I tried a second machine and Excel behaved as we all expect.
On the first machine, I found A1 was "Fri ", rather than "Fri".
I factored "1 Fri 4" [the first Friday in April" into "1 ", "Fri " and "4".
When I corrected the data, Excel worked as I intended.

Is there any way to get Excel to display spaces as graphic characters?
E.g. in RegexBuddy, a product I find very useful, "Fri " displays as "Fri·".
That last character was produced by Insert Symbol MIDDLE DOT.
I googled Excel display spaces and found a suggestion like using
=SUBSTITUTE(A1," ","·") in an auxiliary column.

Thank you both again for helping me to learn from my mistakes. ;)
 
Hi Walter,

Am Sun, 29 Jun 2014 08:45:36 +0100 schrieb Walter Briscoe:
I tried a second machine and Excel behaved as we all expect.
On the first machine, I found A1 was "Fri ", rather than "Fri".
I factored "1 Fri 4" [the first Friday in April" into "1 ", "Fri " and "4".
When I corrected the data, Excel worked as I intended.
Click to expand...

if you get unexpected outputs check your data for leading or trailing
spaces e.g. with LEN
Leading and trailing spaces you can easiliy eliminate with
TextToColumns => FixedWidth => Finish.


Regards
Claus B.
 
Your use of WEEKDAY is much more economical than my use of HLOOKUP.
Well it would be if WEEKDAY took a string argument, rather than a serial
number. If A1 is "Fri" and A2 is =WEEKDAY(A1), A2 evaluates as #VALUE!
OTOH
=HLOOKUP(A1,{"Fri","Mon","Sat","Sun","Thu","Tue","Wed";5,1,6,7,4,2,3},2,TRUE)
evaluates as 5 which matches what I want.
Click to expand...

Hi Walter,
The pointer in WEEKDAY needs to ref a *date* as I mentioned. So...

A1: 06/29/2014
B1: =WEEKDAY(A1)

...returns 1 since Sunday is the 1st day of the week. Given that your
example data shows Mon to Sat, the returns will be 2 to 7!
 
Back
Top