|
Help with Dates
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").V alue yearendmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB |
Help with Dates
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) "johnboy" wrote in message ... 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").V alue yearendmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB |
Help with Dates
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? -- JB "Bob Phillips" wrote: 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) "johnboy" wrote in message ... 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").V alue yearendmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB |
Help with Dates
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) "johnboy" wrote in message ... 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? -- JB "Bob Phillips" wrote: 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) "johnboy" wrote in message ... 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").V alue yearendmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB |
Help with Dates
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. -- JB "Bob Phillips" wrote: 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) "johnboy" wrote in message ... 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? -- JB "Bob Phillips" wrote: 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) "johnboy" wrote in message ... 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").V alue yearendmonth = ThisWorkbook.Worksheets("YearlyCalendar").Range("S 36").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 -- JB |
| All times are GMT +1. The time now is 02:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com