+ Reply to Thread
Results 1 to 5 of 5

Help with Dates

  1. #1
    johnboy
    Guest

    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").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
    --
    JB

  2. #2
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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
    > --
    > JB




  3. #3
    johnboy
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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
    > > --
    > > JB

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: 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" <[email protected]> wrote in message
    news:[email protected]...
    > 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" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > 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
    > > > --
    > > > JB

    > >
    > >
    > >




  5. #5
    johnboy
    Guest

    Re: 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" <[email protected]> wrote in message
    > news:[email protected]...
    > > 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" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > 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
    > > > > --
    > > > > JB
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1