I have an interesting VBA project for you! My department has a particular schedule that we follow throughout the month. Every workday is called a “Business Day”, but we substitute this and use the abbreviation “BD”. The very first day of each month is “BD1”. However, this day has to fall on a workday (Monday – Friday). If the 1st falls on a Sunday, then Monday would be “BD1” even though it is the 2nd. This is true for the entire month. So I’ll use the current month we are in as an example:
1-Mar BD1
2-Mar weekend
3-Mar weekend
4-Mar BD2
5-Mar BD3
6-Mar BD4
7-Mar BD5
8-Mar BD6
9-Mar weekend
10-Mar weekend
11-Mar BD7
12-Mar BD8
13-Mar BD9
14-Mar BD10
15-Mar BD11
16-Mar weekend
17-Mar weekend
18-Mar BD12
19-Mar BD13
20-Mar BD14
21-Mar BD15
22-Mar BD16
23-Mar weekend
24-Mar weekend
25-Mar BD17
26-Mar BD18
27-Mar BD19
28-Mar BD20
29-Mar BD21
30-Mar weekend
31-Mar weekend
So, this is what I need. I need some code that looks at column B which contains the following date format: 10/05/2012 11:01 AM. One thing to note, there is a space at the beginning of this text. So even though you see “10”, its really “ 10”. This is true for each cell in column B. What I need the code to do, is to incorporate the calendar year and for the code to recognize the pattern of “Business Days” I described earlier. So if I have the following dates, BD should be:
10/05/2012 11:01 AM BD5
09/18/2012 05:12 PM BD12
08/16/2012 04:36 AM BD12
07/24/2012 09:25 AM BD17
07/23/2012 10:07 PM BD16
07/20/2012 02:14 PM BD15
The code needs to look at each cell in column B (number of records will vary), so there needs to be a loop until column B = "". This needs to be completed for each cell so that there is a "BD" assigned for each record.
If you need further explanation or maybe another example, please let me know.
Bookmarks