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?
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 Reafiddy,
If your dates start in A2, then in B2;
=1-WEEKDAY(A2)+7+A2
copied down should do it.
Bill
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?
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
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.
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
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
Bill
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
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
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.
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
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.
If your interested this is what I was trying to do:
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.
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
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
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
Re: Find The Last Date Of The Week
Roger that, I can see it now. Cheers for the info.
Don’t have an account yet? Register yourself now and be a part of our community!