Sort by days of weeks

Question

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!

haytham amairah · Answer

John,
&nbsp;
Is this what you want?

&nbsp;

john bebb · Answer

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

haytham amairah · Answer

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

john bebb · Answer

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 &amp; has a space before the date is commenced. Data may be &gt;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&nbsp;then by event time in hours (or part thereof)... OR I require to sort by&nbsp;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 &amp; time informations from the concatenated column. Thank you again for your assistance. John

haytham amairah · Answer

John,
&nbsp;
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.
&nbsp;
In the screenshot below, I have used this formula to extract the dates values from the text dates.
&nbsp;
=DATE(LEFT(SUBSTITUTE(B2,CHAR(160),""),4),MID(SUBSTITUTE(B2,CHAR(160),""),6,2),MID(SUBSTITUTE(B2,CHAR(160),""),9,2))+RIGHT(B2,8)
&nbsp;
&nbsp;

&nbsp;
I don't think that the formula above is applicable to each case because the text dates can come in many forms.
&nbsp;
It becomes more complicated if the text dates contain times (Dates &amp; 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.
&nbsp;

Forum Discussion

Sort by days of weeks

8 Replies

Resources