Find The Last Date Of The Week

  • Hi all,

    I need a macro to find the date at the end of the week.

    Ie today is saturday the 20th january - Macro would return the value 21/01/07.

    Or IF the date today was the 24/01/07 - Macro would return the value 28/01/07.

    Can anyone help?

  • Re: Find The Last Date Of The Week

    Hi Reafidy,

    What is in a TextBox, the date or the End or Week date, or both? What kind of textbox is it, from the Control Toolbox, Forms Toolbar or Drawing Toolbar?


  • Re: Find The Last Date Of The Week


    Perhaps I better explain myself better I have a list of numbers starting at 1 on a spreadsheet (A1 downwards) that represent dates that are all the end of the week(sun). 1 represents 04/06/06, and so on. I have a user form that for each number in columnA loads the week ending date into combobox2. The dates are always consecutive ie A week can never be missed.

    What I want to do is when the userform is loaded run a sub that checks the last number on the sheet which will represent a week ending and if there is weeks missing between then and now add the numbers to the spreadsheet.

    Its a bit of a messy situation, but I have to have it set out with numbers in the spreadsheet not the actual dates.

    The textboxes are from the control toolbox in vba.

    Thanks for the help.

  • Re: Find The Last Date Of The Week

    Hi Reafidy,

    The following code will add the next week ending date after the last entry in column A, if the last entry is 7 days before today's date. I don't know if this is what you mean.

    Private Sub UserForm_Initialize()
        Dim dNum As Double
        Dim dLatest As Double
        dNum = Date
        dLatest = Sheet1.Range("A65536").End(xlUp).Value
        If dLatest <= dNum - 7 Then
            Sheet1.Range("A65536").End(xlUp).Offset(1, 0).Value = dLatest + 7
        End If
    End Sub


  • Re: Find The Last Date Of The Week

    Ranger - I hope you get a Ozgrid MVP tag pretty soon - you've helped me many times recently. Thanks - I needed a very similar formula to the one you provided : D

    I am new to VBA - comments on how to improve my code are always welcome.

  • Re: Find The Last Date Of The Week

    Hi Bill,

    Firstyl I should point out those were Upside comments above. Though I totally agree! I have had lots of help from you aswell, thanks again.

    Unfortunately the code you posted wasnt quite what im after, which is my fault, as im struggling to explain this properly.

    Basically if someone can come up with the code that if I put a date into a textbox on a userform a msgbox appears with the corresponding week ending date for the date entered into the textbox I will be able to mainpulate the code for what I want and hopefully not waste anymore of anyones time.

    Example - user inputs 25/01/07( A Tuesday) into the textbox the msgbox displays 28/01/07 which is a sunday the week ending date.

    Cheers, apologies for the confusion.

  • Re: Find The Last Date Of The Week

    Hi Reafidy,

    This is totally different from the last post which asked for the number to be added to the spreadsheet if the latest week ending date was missing.

    If you add a label to your form (Label1 in my code, probably different in yours), with the Visible property set to False, then add the following code to TextBox1 (may be different in yours) Before_Update event.

    Hopefully this is what you are looking for.


  • Re: Find The Last Date Of The Week

    Is this what you are after?

  • Re: Find The Last Date Of The Week

    Hi Chaps,

    Thanks for the help, this ones solved. Basically I was looking for the bit of code you supplied below. Sorry for the confusion.

    dWeekEnd = 1 - Weekday(dDate) + 7 + dDate

    If your interested this is what I was trying to do:

    Its a bit of a mess but it works. Thanks again Ranger and Weevil, much appreciated.

  • Re: Find The Last Date Of The Week

    Hey Reafidy ,

    I think a bit of an issue with your formula might be that if the original date you enter is itself a Sunday it will tell you that the next Sunday is the last day of the week.

    That’s why I included the

    + Int(Weekday(inputDate) = 1

    bits in my code.

    May not be a problem for you, just an interesting little additional twist in the problem.

  • Re: Find The Last Date Of The Week


    + Int(Weekday(inputDate) = 1)

    Just adds negative 1 to the equation if the Input Date is a Sunday, or adds zero if it is any other day.

    Gotta do it on both sides to balance the equation though.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!