Hi Everybody,
This post can be treated as a continuation to : http://excel-crusade.blogspot.in/2014/08/week-startingending-formulation-smart.html
Because here I will be pleasing the VBA lovers, to the week start/ending in an even more simpler fashion. Even if you are not a VBA expert, you can learn CASE function and Application.WorksheetFunction using this exercise, which is actually a good starting point
Now, by using VBA we are going to write a custom function for Week start using the logic mentioned in the above mentioned post. Similar to the previous post, lets setup our pre-requisite informations:
1. I need a week start
2. My business week starts on Monday
Step 1: Get Daily date input from excel
Alt + F11 -> Create function like the following
DailyDate is the variable which is going to the store the input from excel
Step 2: Figure out the day of week
VBA allows the user to optimise his/her code using the application functions. We access the application function by using Application.WorksheetFunction./*any function in the worksheet library*/
For our case, we need WEEKDAY function. This line will assign d the value of day of week of the date starting from Monday
For example: if Dailydate = 11/Aug/2014,Monday then d = 1. if Dailydate = 12/Aug/2014,Tuesday then d =2. So on.....
Step 3: Design Case logic
Case is a logical function used to assign values based on the conditions met. The following are the conditions in our case
How to interpret the function : if d = 1(Monday), then weekstart will be the same date (which is Monday). If d = 2(Tuesday) , then weekstart will be one day less than that day ( which is Tuesday). and So on upto Sunday
Step 4: Use the function in the sheet
Initially on using the formula like this,
you will be getting something like this.
Nothing to panic. It is just the numerical value of that date. Simply change the number format to have them in the date format
Happy Coding
This post can be treated as a continuation to : http://excel-crusade.blogspot.in/2014/08/week-startingending-formulation-smart.html
Because here I will be pleasing the VBA lovers, to the week start/ending in an even more simpler fashion. Even if you are not a VBA expert, you can learn CASE function and Application.WorksheetFunction using this exercise, which is actually a good starting point
Now, by using VBA we are going to write a custom function for Week start using the logic mentioned in the above mentioned post. Similar to the previous post, lets setup our pre-requisite informations:
1. I need a week start
2. My business week starts on Monday
Step 1: Get Daily date input from excel
Alt + F11 -> Create function like the following
Step 2: Figure out the day of week
VBA allows the user to optimise his/her code using the application functions. We access the application function by using Application.WorksheetFunction./*any function in the worksheet library*/
For our case, we need WEEKDAY function. This line will assign d the value of day of week of the date starting from Monday
For example: if Dailydate = 11/Aug/2014,Monday then d = 1. if Dailydate = 12/Aug/2014,Tuesday then d =2. So on.....
Step 3: Design Case logic
Case is a logical function used to assign values based on the conditions met. The following are the conditions in our case
How to interpret the function : if d = 1(Monday), then weekstart will be the same date (which is Monday). If d = 2(Tuesday) , then weekstart will be one day less than that day ( which is Tuesday). and So on upto Sunday
Step 4: Use the function in the sheet
Initially on using the formula like this,
Happy Coding
No comments:
Post a Comment