How to sort by day of week

I think I would add in a translator table to assign a numerical value to each day of the week:

B2=IFERROR(VLOOKUP(A2, Day Of Week Value, 2, 0), "")

select B2 and fill down, then sort on column B (DOW Value)

I hope this helps

Hi Chap,

Since column A contains DAte and time values, there's no need to use a separate table an VLOOKUP to determine the DoW number in column B. That can be done using the WEEKDAY function:

B2, and fill down: =WEEKDAY(A2)

On the left is the original table. Column A is formatted to show only the Weekday of the Date and Time value in each cell (see the entry box for the value in A2).

Column B contains the formula above, and may be hidden.

The table on the right is a copy of the first table, sorted ascending on column B.

Regards,

Barry

Chap Harrison wrote:

1

This won't work for me, but you helped clarify my understanding: My table's Day-of-Week column really is only the day of the week. There's really not a specific date behind it. This is a table of weekly meetings, and what day of the week each one meets on.

2

I took a look at my "time-of-day" column, and saw that in the process of setting the custom cell format, each cell had been quietly converted to a date/time value, with a date of today. It doesn't really matter, I guess - all that's being displayed is the ToD, and it sorts the way I want it to.

3

I guess my take-away is that neither of those columns is a true date/time value, and so the best approach is the one Wayne suggested, employing a numerical code (for sorting) and a function that transforms the code into the proper displayable format.

1

This threw me off:

"I created a custom cell format of type "Date & Time" consisting of only a "Day of Week" element. I then selected the column in the spreadsheet, and used the Inspector to assign my new custom cell format to that column's cell format."

If you've entered only the name of the day, this custom format will have no effect. The cell contains a text string; the format applies only when the cell contains a Date and Time value, and determines how that value is displayed.

2

Setting the format had no effect on the contents of these cells. When you entered the time part of a Date and Time value, Numbers completed the D&T value, using the Date on which the entry was created as the Date part. Similarly, when you enter only the Date part of a D&T value, Numbers completes the value, using 00:00:00 as the Time part, making the full D&T value 'midnight, at the beginning of the entered date.' ALL cells showing a ToD or a Date contain a Date and Time value. Cells showing a ToD will sort as expected, provided all have the same Date as the Date part of their D&T value.

3

Agreed wrt your DoW column, which contains a text string, not a D&T value. An alternate to the use of a Lookup table to assign a number to each day name would be to attach that number to the beginning of the day name (eg. 1 Monday, 2 Tuesday, etc), inserting these values into a pop-up menu cell, then filling the cell down column A, and using the pop-up to choose the day name for each row. Example below.

Your ToD column, though is a set of "true date/time values", all of which display only the Time part, and all of which, at this point, contain the same value in their Date part.

Example using leading 'day of week' numbers. Table on left is the original order, on right, same table is sorted ascending. All cells contain text values.

Regards,

Barry

Another approach would be to make your entries numeric rather than text, using numbers related to days of the week, then use DAYNAME to display the text name of the day in another column. In this scheme the days run from 1 for Sunday to 7 for Saturday.

DAYNAME(2) returns Monday. Then sort on the entry value column.

Jerry