Find The Last Date Of The Week

**Reafidy** · Jan 20th 2007

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?

Ranger · Jan 20th 2007

Re: Find The Last Date Of The Week

Hi Reafiddy,

If your dates start in A2, then in B2;

=1-WEEKDAY(A2)+7+A2

copied down should do it.

Bill

**Reafidy** · Jan 20th 2007

Re: Find The Last Date Of The Week

Hey Bill,

Thanks for the reply, that works. However I am trying to do it withcode. Ie the date is in a text box, any ideas?

Ranger · Jan 20th 2007

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?

Bill

**Reafidy** · Jan 20th 2007

Re: Find The Last Date Of The Week

Bill,

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.

Code

Dim LogRng As Range
Private Sub UserForm_Initialize()   


    Set LogRng = Range("A2", Cells(Rows.Count, 1).End(xlUp).Offset(0, 9))
    Call LdArcrft
    Call LdWkEnd
    With ComboBox2
        .Value = .List(.ListCount - 1, 0)
    End With
    ComboBox1.Value = Range("N2")
    
End Sub
Private Function LdArcrft()


    With ComboBox1  'List Of Aircraft Types
        .AddItem "Bell 206 B Jetranger"
        .AddItem "Hughes 269C"
        .AddItem "Eurocopter AS 350A Ecureuil"
        .AddItem "Eurocopter AS 350B Ecureuil"
        .AddItem "Eurocopter AS 350BA Ecureuil"
        .AddItem "Eurocopter AS 350B2 Ecureuil"
        .AddItem "Eurocopter AS 350B3 Ecureuil"
        .AddItem "Eurocopter AS 355N Ecureuil"
        .AddItem "Eurocopter AS 355F Ecureuil"
        .AddItem "Eurocopter EC 120"
        .AddItem "Eurocopter EC 135"
        .AddItem "Eurocopter EC 145"
    End With


End Function
Private Function LdWkEnd() As Boolean
    Dim StartDate As Date
    Dim x As Integer


    StartDate = Range("N3").Value
    For Each cell In LogRng.Columns(1).Cells
        Me.ComboBox2.AddItem CStr(DateAdd("d", 7 * x, StartDate))
        x = x + 1
    Next cell


End Function
Private Sub ComboBox1_Change()


    Call LoadData(ComboBox1.ListIndex)


End Sub

Display More

Ranger · Jan 20th 2007

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.

Code

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

Bill

Upside · Jan 20th 2007

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

Ranger · Jan 20th 2007

Re: Find The Last Date Of The Week

Hi Reafidy,

Thank you very much for your comments, they are most appreciated. Only too glad to help.

Bill

**Reafidy** · Jan 21st 2007

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.

Ranger · Jan 21st 2007

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.

Code

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim dDate As Date
    Dim dWeekEnd As Date
    If TextBox1.Value <> "" Then
        Label1.Visible = True
    Else
        Label1.Visible = False
        Exit Sub
    End If
    Me.TextBox1.Value = Format(Me.TextBox1.Value, "dd/mm/yy")
    dDate = Format(Me.TextBox1, shortdate)
    dWeekEnd = 1 - Weekday(dDate) + 7 + dDate
    Label1.Caption = dWeekEnd
End Sub

Display More

Hopefully this is what you are looking for.

Bill

weevil · Jan 21st 2007

Re: Find The Last Date Of The Week

Is this what you are after?

Code

Private Sub CommandButton1_Click()


    If IsDate(TextBox1) Then
    
        inputDate = DateValue(TextBox1)
    
        MsgBox (DateValue(inputDate + Int(Weekday(inputDate) = 1)) - _
            Weekday(inputDate + Int(Weekday(inputDate) = 1)) + 8)
    
    End If


End Sub

Display More

**Reafidy** · Jan 22nd 2007

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.

Code

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

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

Code

Dim LogRng As Range
Dim ToDateRng As Range
Dim RowNo As Integer
Private Function LdWkEnd() As Boolean
    Dim StartDate As Date
    Dim dDate As Date
    Dim dWeekEnd As Date
    Dim dLatest As Date
    Dim x As Integer
    Dim EntryRng As Range
    
    Set EntryRng = Range("A2", Range("A65536").End(xlUp))
    StartDate = Range("N3").Value
    For Each cell In EntryRng
        Me.ComboBox2.AddItem CStr(DateAdd("d", 7 * x, StartDate))
        x = x + 1
    Next cell


    dDate = Format(Now, "dd/mm/yy")
    dLatest = ComboBox2.List(ComboBox2.ListCount - 1, 0)
    dWeekEnd = 1 - Weekday(dDate) + 7 + dDate


    Do Until dWeekEnd < dLatest + 14
        Range("A65536").End(xlUp).Offset(1, 0).Value = Range("A65536").End(xlUp) + 1
        dLatest = dLatest + 7
        Me.ComboBox2.AddItem Format(dLatest, "dd/mm/yy")
    Loop
    
    Set LogRng = Range("A2", Cells(Rows.Count, 1).End(xlUp).Offset(0, 9))
    
    ListBox1.RowSource = LogRng.Address(external:=True)
    
    With ComboBox2
        .ListIndex = .ListCount - 1
    End With
    
End Function

Display More

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

weevil · Jan 22nd 2007

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

Code

+ Int(Weekday(inputDate) = 1

bits in my code.

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

**Reafidy** · Jan 22nd 2007

Re: Find The Last Date Of The Week

Hmmm I see, thanks weevil, takin that on board but just had a few beers so will ponder it properly another more suitable day. : D

weevil · Jan 22nd 2007

Re: Find The Last Date Of The Week

Code

+ 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.

**Reafidy** · Jan 22nd 2007

Re: Find The Last Date Of The Week

Roger that, I can see it now. Cheers for the info.

Find The Last Date Of The Week

Participate now!

Share