Organize/Sort large data Part 2 - Extracting Data at Specific day of the Week.

In the previous post , I explained how large data can be organized by using Pivot Table. In this post, I will extend this example by describing to you how you can extract data that is on particular day of the week, e.g. Wednesday. If that day is a holiday we want it to take the value before or after that.

Step 1.

Choose the symbol of your liking, in this case "FRI" is used.
Copy and paste this filtered data obtained,  into a new worksheet, in this case, "Sheet3".

Step 2.

Next, noticed that the format of the date represented here is YYYYMMDD. In this format, Excel is not able to make out the date. Hence, you will need to translate the date into Excel readable date format. This steps would vary depending on the format used and you might need some creativity to translate it to excel readable format. In this example, we do truncation using "LEFT" and "RIGHT" function of Excel. Which obtain part of a string starting from the right or left. The following formula will enable us to do the translation:

DATE(LEFT(A2,4),LEFT(RIGHT(A2,4),2),RIGHT(A2,2))

Step 3.

Now Excel is able to read the date properly however, you need to extract the weekdays information and this can be done using:

 WEEKDAY(B4,1)

The first parameter correspond to the Date cell we converted in Step 2 and the second parameter of  allows you to choose different representation of weekdays. In this case, we chose 1, which means the start of the week as Sunday represented by 1 and the end of the week as Saturday represented by 7; Wednesday is represented by 4.


Explanation of what I will be doing, you may choose to skip if you just want to get to the results:
In the case where we are certain that we have data on every Wednesday, we will be able to use a simple method by using FILTERS or a simple IF statement "IF(C5=3,D5,)".

But the problem here is we could have an holiday on Wednesday and we will not be able to get any data for that week. I need to get the closest data to Wednesday. What I am doing here is to create a convention where I can identify the closest day to Wednesday in the event of a Holiday. By making Wednesday the start of the week, it will have the smallest digit which can be identified by comparing with the previous day.

You might like to notice that none of the option in the WEEKDAY() second parameter allows us to let us start the week on Wednesday. Hence, we need to use another method to remap the weekdays.


Step 4.

We need to remap the weekday as per my previous explanation. To do this I created a Lookup Table  in "Sheet2!$C$3:$D$7" and it looks like this:


This table translate the week such that it will start on Wednesday.

By using this table and the formula:

VLOOKUP(C2,Sheet2!$C$3:$D$7,2)

Create a new column using this formula and you would have recreated the new Weekday field by starting the week from Wednesday which is represented by the smallest number of the week, 1.

Step 5.

Noticed that the start of the week is the smallest number. From this Wednesday to the coming Tuesday, the new Weekday number will be in increasing order and it is only when there is a transition to the next week, then new weekday number will suddenly be smaller. By using this pattern, use a IF statement in a new column to filter out the starting day of the week:

IF(D6<D7,E6,"")

Hence, when there is a holiday on Wednesday, it will automatically take the next working day of the week as the start of the week. Now you just need to filter out the BLANKS.

VIOLA! You got the data you need!

The finished product can be obtained here.