Forum Discussion
Sort by days of weeks
John,
Is this what you want?
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
Please provide us with a sample of your data to figure out how to manipulate it.
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. JohnJohn,
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.