One very common task which all of us come across in our daily business data preparation is combining several dates into a weekly data. By weekly data, It can be a week start or week ending and the beginning or ending of the week can vary from Monday to Sunday. So we have 14 ways (2X7) of combining daily data into weekly data.
In
this exercise we will learn how to process weekly dates efficiently.
Let us have some random
dates, just like the one below. 
1. Is it a week ending or week
starting
2. Day of week start or week
ending
Illustration:
My business week starts every Monday
Week Starting
Calculation:
Let’s
have Monday Week Start. So what does it Mean ? It means we need to tag a day of
week as its Monday’s Date. Not Clear. Please observe below
Now
that we understood what we want, let’s do what we need to
Step 1: Determine Day of the week with reference to the start of week. Use the Weekday Formula
Step 1: Determine Day of the week with reference to the start of week. Use the Weekday Formula
·
Serial_number
= Order Date (A2)
·
[return_type]
= 2
If you
provide ‘2’ for the [return_type], then it will return 1
when Monday, 2 when Tuesday and so on.
Step 2: Create a Dummy range (anywhere)
like this and name it as WS. 
So
this range in context to the exercise is important in determining what value to
add to the existing date so that it will be tagged as the Monday week start.
For
ex: Tuesday means 2 as per day of
week formula. If 2 then (-1) should be
added so that it is tagged as the Monday’s date.
Step 3: Formulate using Vlookup to find
out what should be added (Lets avoid the gigantic if function)
So the
Syntax goes like this
·
Lookup_value
= the Day of week (B2)
·
Table_array
= WS (the range that has to be looked at)
·
Col_index_num
= 2 ( Return the corresponding value in 2nd column if a match is
found)
·
[range_lookup]
= FALSE (Optional – used to match out exact value/case)
Step 4: Now add the value to the
original date
Optimizing the steps: Instead of having so many
cells, let’s have just one cell with a super-dooper nested formula like this
Adding
Legends/Indicators: Some
business requires legends that to Indicate that it is Week start by prefixing
WS in front of the dates (WE for Week endings).
Please
try concatenating “WS “ and the corresponding date , so that you will figure
out that you will land up something like this as a result “WS 39818” Which
wrong partially, because only the number value of the date is concatenated and
not the date as it is.
So we
need to convert this number value in to a date value and then concatenate
Syntax
: Concatenate the following 2 strings:
1. String 1: “WS “ (Make sure you
don’t miss the space )
2. String 2: is formula like this
a. Text(Value,Format)
b. Value = B2 ( the calculated
Week start)
c.
Format
= “DD-MMM” ( Don’t forget the quotes )
Some
business likes to have “MMM-DD” Format as well
Week ending calculation
Let’s
have a Sunday week ending. i.e Business Starts from Monday and ends on Sunday .
Just add 6 to the previous calculation.
But if
you want to work from, Steps are very similar. One just need to recheck the
logic.
Logic change:
1. Change return type for day of
week with respect to the end of week
2. Previously we added negative
numbers, so that it will move towards the start of week. Now as simple as it is
, we need to positive numbers, so that it will increase towards the end of
week. So as per this range, (WS) now if day of week = 1(Sunday) then add 0, if
day of week = 2 then add 6
Syntax
Change
1. Day
of week formula
: As mentioned in the logic, [Return_type]=1
2.
Rename the Dummy range as WE
3.
Vlookup: table_array = WE
4.
Concatenate Formula: String1 = “WE “ (This makes
more sense for the Week Ending)
So the final Output will look
like this for Week ending calculation
No comments:
Post a Comment