none
date code RRS feed

  • Question

  • Good afternoon all.

    I'm working on a spreadsheet package---- dozens of workbooks which will be updated with new data weekly/monthly.

    I have to update the dates in specific cells based on the user's input.

    The user will be updating the worksheet on a weekly basis, except at the beginning of the month, and the end of the month.

    The activity will be done each Friday, and will cover the week from the previous Friday (8 days earlier) to the following Thursday (1 day prior to the Friday in question).

    Then, at the beginning of the month, they'll skip a week, because the month's end will fall at some point in the middle of the week, which will result in the timing of the month end activity cutting off the amount of days included.

    And they always want the data to be associated with its respective month.

    So, using the end of October, and beginning of November as examples.

    They'd pull the data from the 21st through the 27th on the 28th of October.

    They'd then pull the data from the 28th through the 31st on Nov. 4th.

    Then, on November 11th, they'd pull the data from Nov. 1st through Nov. 10th.

    the following week would "go back to normal."

    Each subsequent Friday through Thursday for the middle of the month will be pulled on the subsequent Friday.

    Then at the end of the month, we redo this process again.

    I need to set up a macro which will set the dates based on this format I described.

    While I agree this is a screwy way, this has apparently been a long-standing practice, and I need to find a way to automate using VBA.

    I will be using a "Mo/Day" format for the date inputs, 10/31, 9/25, 1/13, etc.....

    TYIA.

    Sunday, October 30, 2016 12:20 AM

Answers

  • Hi,

    In VBA, you could use the code below to get some specific days:

    Sub returnDate()
    Dim t As Date
    t = Date
    't = CDate("")
    previousFri = t - Weekday(t + 1)
    nextFri = t + 7 - Weekday(t + 1)
    Debug.Print t; previousFri; nextFri
     
    Dim lastDayLastMonth As Date
    lastDayLastMonth = Application.WorksheetFunction.EoMonth(t, -1) 'last day of last month
    lastFriLastMonth = lastDayLastMonth + 1 - Weekday(lastDayLastMonth - 5) 'last Friday of last month
    Debug.Print lastDayLastMonth; lastFriLastMonth
     
    Dim firstDayThisMonth As Date
    'firstDayThisMonth = CDate(Year(t) & "/" & Month(t) & "/" & "1") 'the 1st day of this month
    firstDayThisMonth = Application.WorksheetFunction.EoMonth(t, -1) + 1 'the 1st day of this month
    firstFri = firstDayThisMonth + 7 - Weekday(firstDayThisMonth + 1) ' the 1st Fridy of this month
    secondThu = firstDayThisMonth + 13 - Weekday(firstDayThisMonth + 1) 'the 2nd Thursday of this month
    Debug.Print firstDayThisMonth; firstFri; secondThu
     
    End Sub

    Regards,

    Celeste




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SteveDB1 Monday, November 14, 2016 12:31 AM
    Sunday, November 6, 2016 9:43 AM
    Moderator

All replies

  • Hi,

    Do you want to list the day when you need to pull the data or the date of the data?

    Skipping a week means skip the data in the first week or that day you don’t need to pull data?

     

    Is the following date right?

    In October, skip the first week: 2016/10/1.

    10/7 needs data from 10/1 to 10/6;

    Then every Friday gets data from the last Friday to this Thursday.

    In November,

    11/4 needs data from 10/28 to 10/31;

    11/11 needs data from 11/1 to 11/10;

    Then every Friday gets data from the last Friday to this Thursday.

    Basically, the first Friday gets data from the last Friday of the last month to the end of the last month.

    The second Friday gets data form the beginning of the month to the second Thursday.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, October 31, 2016 10:00 AM
    Moderator
  • Hi,

    Do you want to list the day when you need to pull the data or the date of the data?

    Skipping a week means skip the data in the first week or that day you don’t need to pull data?

     

    Is the following date right?

    In October, skip the first week: 2016/10/1.

    10/7 needs data from 10/1 to 10/6;

    Then every Friday gets data from the last Friday to this Thursday.

    In November,

    11/4 needs data from 10/28 to 10/31;

    11/11 needs data from 11/1 to 11/10;

    Then every Friday gets data from the last Friday to this Thursday.

    Basically, the first Friday gets data from the last Friday of the last month to the end of the last month.

    The second Friday gets data form the beginning of the month to the second Thursday.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Hi Celeste.

    First question: Date of the data.

    Part of the problem here has to do with the client wanting to keep each month's data within its own month. So, even if the month ends/begins in the middle of the week, they only want the dates from the previous Thursday. And then when the new month starts, they want to skip it for the first week (in the case of November, they could pull the 28th thru the 31st, AND the 1st through the 3rd, but they do not on the 4th. They wait until the 11th to pull the 1st thru the 10th, while only pulling the data from the 28th thru the 31st on the 4th ).

    It'd be a whole lot less complicated to just keep pulling data each Friday, regardless, and then distributing that data to the respective month's files. Oh, something else--- there's a new workbook for each month. So, WkBk10 is for October, while WkBk11 is for November, etc...... WkBk's 1 thru 12, starting over each new year. I'll deal with the nomenclature on my end. Apparently a previous issue arose and the workbook became too complicated to make any sense out of. I may see if they'll let me deal with a single year, and 12 worksheets. Sorry.... my rantings.....

    Your description sounds accurate.

    TYIA.

    Thursday, November 3, 2016 2:04 AM
  • Hi,

    In VBA, you could use the code below to get some specific days:

    Sub returnDate()
    Dim t As Date
    t = Date
    't = CDate("")
    previousFri = t - Weekday(t + 1)
    nextFri = t + 7 - Weekday(t + 1)
    Debug.Print t; previousFri; nextFri
     
    Dim lastDayLastMonth As Date
    lastDayLastMonth = Application.WorksheetFunction.EoMonth(t, -1) 'last day of last month
    lastFriLastMonth = lastDayLastMonth + 1 - Weekday(lastDayLastMonth - 5) 'last Friday of last month
    Debug.Print lastDayLastMonth; lastFriLastMonth
     
    Dim firstDayThisMonth As Date
    'firstDayThisMonth = CDate(Year(t) & "/" & Month(t) & "/" & "1") 'the 1st day of this month
    firstDayThisMonth = Application.WorksheetFunction.EoMonth(t, -1) + 1 'the 1st day of this month
    firstFri = firstDayThisMonth + 7 - Weekday(firstDayThisMonth + 1) ' the 1st Fridy of this month
    secondThu = firstDayThisMonth + 13 - Weekday(firstDayThisMonth + 1) 'the 2nd Thursday of this month
    Debug.Print firstDayThisMonth; firstFri; secondThu
     
    End Sub

    Regards,

    Celeste




    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by SteveDB1 Monday, November 14, 2016 12:31 AM
    Sunday, November 6, 2016 9:43 AM
    Moderator
  • Thank you.

    This does exactly what I'm looking to accomplish.

    Monday, November 14, 2016 12:31 AM