Sort by days of weeks

[John Bebb]

Hi Folks - frustration on a simple? issue of sorting by days
I have several months or years of timed daily data - I wish to sort and list by the individual day only.
The day may be specified in a date code string which contains month &/or year data.
I wish to achieve (e.g)
All Monday events contiguously... then all Tuesday events, then all Wednesday events... etc.
 as they occured for the entire data period... excel seems determined to list the days of the week sequentially by the weeks & months of the bulk data... your suggestion, please!

[#2]

John,

Is this what you want?

[#3]

Hi Haytham - thank you for your response. Yes - that is the required function. I have since been able to achieve it by using the =WEEKDAY() function.
I am having considerable difficulty in parsing date&time as concatenated into one cell and then correctly sorting the data sequence by day &/or time as required.
My data comes from various sources, each of which may format d&t somewhat differently in both sequence and spacing(s)... and on occasion parsing corrupts either the date or the time fields!

Cheers, John

[#4]

Please provide us with a sample of your data to figure out how to manipulate it.

[#5]

Hello Haytham, - thank you for your message and interest.
I attach a sample of original data - but such arrives from various sources and the date and time information sequencing, spacing and formatting is not consistent through the different sources.
E.g. the sample data the date is given in reverse order & has a space before the date is commenced.
Data may be >20 lines. If you can show me the correct methodology for colB of this sample, I would expect to be able to apply similar techniques to cover each individual source.
I require to sort either by weekday (as your previous given solution) and then by event time in hours (or part thereof)... OR I require to sort by event time in hours (or part thereof) and then by weekday...
Obviously sorting by other parameters will follow but such is not problematic... my problem is to derive the correct weekday & time informations from the concatenated column.
Thank you again for your assistance. John

[#6]

John,

In order to take advantage of dates in Excel, and to perform the necessary operations on them, such as sorting and calculating, you have to make sure that these dates are actual dates (values) not text strings.

In the screenshot below, I have used this formula to extract the dates values from the text dates.

=DATE(LEFT(SUBSTITUTE(B2,CHAR(160),""),4),MID(SUBSTITUTE(B2,CHAR(160),""),6,2),MID(SUBSTITUTE(B2,CHAR(160),""),9,2))+RIGHT(B2,8)

I don't think that the formula above is applicable to each case because the text dates can come in many forms.

It becomes more complicated if the text dates contain times (Dates & Times), and spaces.

It becomes more and more complicated if these spaces are not normal spaces where we cannot remove them using the straightforward function: TRIM.

The solution in removing them is to use the SUBSTITUTE function as the formula above.

[#7]

Haytham - my sincere thanks for your efforts and explanations.
I shall peruse with interest... (and some confusion.... from whence are the repetitive statement CHAR(160) derived?) .. anyways - I'll look into that later as I am now able to process the data as desired. Thank you.
Sincere regards, John

[#8]

CHAR 160 is a non-breaking space, my goal of this is to alert you to the existence of this kind of spaces in your data and how to get rid of it.

To prevent confusion and since that space is found at the beginning of each cell, we can ignore it, and shorten the formula by this one:

=DATE(MID(B2,2,4),MID(B2,7,2),MID(B2,10,2))+RIGHT(B2,8)

You will get the same result.

[#9]

Haytham - thank you for the info,
Cheers, John