+ Reply to Thread
Results 1 to 11 of 11

Making calendar dates specific to a pattern

  1. #1
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Making calendar dates specific to a pattern

    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.

  2. #2
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Making calendar dates specific to a pattern

    Hi
    i have a worksheet formula

    Please Login or Register  to view this content.
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  3. #3
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Re: Making calendar dates specific to a pattern

    Sean,

    Very nice, this works well! I am surprised no VBA was needed. Thanks!

  4. #4
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Making calendar dates specific to a pattern

    Thats the majic of excel!
    glad to help

    dont forget to mark it solved & add rep if you will

    thanks

  5. #5
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Re: Making calendar dates specific to a pattern

    Hey Sean,

    I have one other request... Not sure if this is something a formula can do or if this needs to be done through VBA. But I now need to classify what week the "Business Day" falls on. We classify them out of 5 possibilities:

    Week 1
    Week 2
    Week 3
    Week 4
    Week 5

    Let's say BD1 falls on a Thursday. Then BD1 & BD2 are considered to be Week 1. Then BD3 - BD7 is considered Week 2, and this pattern continues until there are no more Business Days. Another example is the following: If BD1 falls on a Monday, then BD1 - BD5 is considered Week 1. Then BD6 - BD10 is considered Week 2 and the pattern continues. Is there a formula that will satisfy this? Or do you know some code that will complete this task?

    Thanks!

  6. #6
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Making calendar dates specific to a pattern

    This should do the trick

    Please Login or Register  to view this content.

  7. #7
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Making calendar dates specific to a pattern

    if you need Week in front

    Please Login or Register  to view this content.
    Sorry forgot to put the trim part in the last thread

  8. #8
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Re: Making calendar dates specific to a pattern

    Hey Sean,

    That almost worked...But not quite. The math is off a bit.

    *******Formula: Should Be:

    10/5/2012 Week 2 Week 1 * 10/5/2012 is a Friday and is BD5 -- Falls in Week 1
    9/18/2012 Week 4 Week 3 * 09/18/2012 is a Wednesday and is BD12 -- Falls in Week 3
    8/16/2012 Week 3 Week 3
    7/24/2012 Week 5 Week 4
    7/23/2012 Week 5 Week 4

  9. #9
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Making calendar dates specific to a pattern

    Hi there are a number of ways to do it,
    various results come up depending on date type
    this one seems to be the most accurate

    Please Login or Register  to view this content.

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Making calendar dates specific to a pattern

    Try this formula for the correct week number

    =IF(WEEKDAY(A2,2)>5,"","Week "&INT((11+DAY(A2)-WEEKDAY(A2-1))/7))

    that will return a blank if A2 is a weekend date.....
    Audere est facere

  11. #11
    Forum Contributor
    Join Date
    08-17-2012
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2007
    Posts
    158

    Re: Making calendar dates specific to a pattern

    Thanks Sean, works like a charm

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Making calendar dates specific to a pattern

    Quote Originally Posted by Sean Thomas View Post
    =INT((DAY(A2)-1)/7)+1
    ....but this won't take the day of week in to account so Friday 22nd and Monday 25th March 2013 both give the same week number (4) even though they are in different weeks - my suggestion will always give you the same week number for one week Monday to Friday, assuming that week is wholly within a single month

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1