Help with Dates

[Guest]

Hi all,
I am trying to populate a combobox for timesheet application with all week
ending dates for our accounting year. Our year runs from April 1st - March
31st.

Code below shows where I have got to so far and works in part. However, I
can't figure out how to stop the code after the end of the accounting year
month.

variable values are obtained as follows:
Startweek value is the next timesheet date in fomat dd/mm/yyyy
This value would start at first week ending date for accounting year and is
incremented by 1 week each time timsheet is submitted.

yearendmonth value is the month / year date accounting period ends in format
dd/mm/yyyy this value is taken from an excel spreadsheet calendar which I
update each year.

How would i need to alter the code to show all week ending dates for each
month in the accounting year only?
Hope I have made myself clear - if someone could kindly assist me with this
please, I would be most grateful.


Private Sub UserForm_Initialize()
Dim startweek As Date
Dim yearendmonth As Date
startweek = ThisWorkbook.Worksheets("Timesheet").Range("K8").Value
yearendmonth =
ThisWorkbook.Worksheets("YearlyCalendar").Range("S36").Value

hyear = Year(yearendmonth)

With ComboBox1
.Clear
NextDate = DateValue(startweek)
Do
.AddItem Format(NextDate, "dd/mm/yyyy")
WeekDate = NextDate
IntervalType = "ww"
Number = 1

'change week ending to next sunday
NextDate = DateAdd(IntervalType, Number, WeekDate)

Loop Until Year(NextDate) > hyear
.Text = startweek
End With
End Sub

 

[Bob Phillips]

Change this line

Loop Until Year(NextDate) > hyear

to

Loop Until NextDate > yearendmonth

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,
thanks for reply - I forgot to mention that accounting year will be changing
to Jan - Dec & in this case change you mention does not work. How can I adapt
code to take this in to consideration?

 

[Bob Phillips]

Why not? It all depends upon the correct start and end dates, and is
automatic from there, nothing in the code.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Bob,
thanks for response - your solution did work, but whilst awaiting replies, i
had made some changes which upset the result.
I have now solved problem by using DateDiff - this counts all the week
ending dates needed to populate combobox using either Apr - Mar or Jan - Dec
accounting periods.