Page 1 of 2 12 LastLast
Results 1 to 10 of 11

How to generate a list with the date of every other Friday.

This is a discussion on How to generate a list with the date of every other Friday. within the Excel Questions forums, part of the Question Forums category; Greetings! I'm brand new to this board and just beginning to learn VBA , so please bear with me. I'm ...

  1. #1
    New Member
    Join Date
    May 2009
    Posts
    4

    Default How to generate a list with the date of every other Friday.

    Greetings!

    I'm brand new to this board and just beginning to learn VBA, so please bear with me. I'm running Windows XP Pro and I use Excel 2003 & 2007, but am using 2003 for this project.

    Ok, here's the scenario. At work, I've been asked to redesign a timesheet for our hourly employees. I'm probably overdoing it, but it is a great opportunity for me to learn some VBA. I've got most of my programming done, but I could use some help figuring out something that has to do with a date.

    On my timesheet, there is a button that, when clicked, allows you to select a pay period end date. Our pay periods end every other Friday. When the button is clicked, it opens a form I designed that has a listbox that I manually entered every pay period for the year into. Here is the code I used:

    Private Sub UserForm_Initialize()
    ' Populate the ListBox control.
    listPayPeriodEndDates.AddItem "04/03/09"
    listPayPeriodEndDates.AddItem "04/17/09"
    listPayPeriodEndDates.AddItem "05/01/09"
    listPayPeriodEndDates.AddItem "05/15/09"
    listPayPeriodEndDates.AddItem "05/29/09"
    listPayPeriodEndDates.AddItem "06/05/09"
    listPayPeriodEndDates.AddItem "06/19/09"
    listPayPeriodEndDates.AddItem "07/03/09"
    listPayPeriodEndDates.AddItem "07/17/09"
    listPayPeriodEndDates.AddItem "07/31/09"
    listPayPeriodEndDates.AddItem "08/07/09"
    listPayPeriodEndDates.AddItem "08/21/09"
    listPayPeriodEndDates.AddItem "09/04/09"
    listPayPeriodEndDates.AddItem "09/18/09"
    listPayPeriodEndDates.AddItem "10/02/09"
    listPayPeriodEndDates.AddItem "10/16/09"
    listPayPeriodEndDates.AddItem "10/30/09"
    listPayPeriodEndDates.AddItem "11/13/09"
    listPayPeriodEndDates.AddItem "11/27/09"
    listPayPeriodEndDates.AddItem "12/04/09"
    listPayPeriodEndDates.AddItem "12/18/09"
    listPayPeriodEndDates.AddItem "01/01/10"
    ' Select the default value
    listPayPeriodEndDates.Value = "04/03/09"
    End Sub

    What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward. Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).

    I'd certainly appreciate any input anyone might have, I'm open to all ideas. Also, it's no big hurry, I'll keep working on it in the meantime.

    Thanks in advance for any help!

    Paul

  2. #2
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,319

    Default Re: How to generate a list with the date of every other Friday.

    Hi and welcome to the board

    Im not very good at VBA, but try this for now, there could be better solutions from someone else

    Code:
    Private Sub UserForm_Initialize()
    s = DateValue("03/04/09")
    listPayPeriodEndDates.Clear
    For i = 1 To 25
        If s > Now() Then listPayPeriodEndDates.AddItem s
        s = s + 14
    Next i
    End Sub
    Last edited by sanrv1f; May 30th, 2009 at 12:42 AM.
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  3. #3
    Board Regular
    Join Date
    May 2009
    Location
    Texas, USA
    Posts
    1,288

    Default Re: How to generate a list with the date of every other Friday.

    Good code!
    Must change 3/4/09 to 3/6/09
    3/4/09 is a Wenesday
    Bill
    Use Option Explicit to avoid chasing code.

  4. #4
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,319

    Default Re: How to generate a list with the date of every other Friday.

    Bill,

    Could you a line after the loop, to set the Min of assigned dates, to be the default value?
    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  5. #5
    GTO
    GTO is offline
    MrExcel MVP
    Join Date
    Dec 2008
    Location
    Phoenix, Arizona
    Posts
    5,067

    Default Re: How to generate a list with the date of every other Friday.

    Quote Originally Posted by TheThird152 View Post
    What I'd like to do instead is have VBA populate the listbox (or combobox or what have you) dynamically, by using a reference point (say, the first pay period of the year) and then populating every other Friday from that point forward.

    Also, it would be great if it could reference the current date as to only list pay period end dates in the future (or even the two prior to todays date, and then maybe 3 or 4 pay period end dates in the future).

    Greetings Paul,

    You mention populating all the paydays from the first pay period of the year forward, but then mention only listing the last couple and the next few future ones. If only looking to get a few near pay dates, not well tested, but try:

    In the Userform's Module:

    Option Explicit

    Private Sub UserForm_Initialize()
        
        '// Either a list or combo box... //
        Me.ListBox1.List = Dates_Return(Date)
        Me.ComboBox1.List = Dates_Return(Date)
    End Sub

    Private Function Dates_Return(InputDate As Date) As Date()
    Dim _
    iCnt                As Long, _
    dtmAddToList        As Date, _
    aryDates(1 To 6)    As Date

    Const DATE_INITIAL As Date = #5/15/2009#

        Do While Not (CLng(InputDate) - CLng(DATE_INITIAL)) Mod 14 = 0
            InputDate = InputDate - 1
        Loop
        
        iCnt = 0
        
        For dtmAddToList = InputDate - 14 To InputDate + 56 Step 14
            iCnt = iCnt + 1
            aryDates(iCnt) = dtmAddToList
        Next
        
        Dates_Return = aryDates
    End Function


    Hope that helps,

    Mark
    Last edited by GTO; May 30th, 2009 at 02:43 AM.

  6. #6
    Board Regular
    Join Date
    May 2009
    Location
    Texas, USA
    Posts
    1,288

    Default Re: How to generate a list with the date of every other Friday.


    listPayPeriodEndDates.AddItem "05/29/09"
    listPayPeriodEndDates.AddItem "06/05/09"


    There appears to be an error in the list of Friday dates. From 5/29 to 6/5 is only one week. The prime date in the procedure may have to be adjusted to get the Friday dates correct. Right now the procedure’s first date in the list box is 6/5/2009 and follows the posted dates after that. The initial value of the list box will always be set equal to the first date in the list.

    I always use comboboxes as you can readily tell what the default item is because it displays in the combobox box. A list box still just shows the list the same with or without the value property being set.




    Code:
     
    Private Sub UserForm_Initialize()
        Dim s As Date
        Dim i As Integer
        Dim First As Boolean
     
        First = True
        s = DateValue("05/8/09")   'prime date
     
        listPayPeriodEndDates.Clear
        For i = 1 To 25
            If s > Now() Then
                listPayPeriodEndDates.AddItem s
                If First = True Then
                    listPayPeriodEndDates.Value = s
                    First = False
                End If
            End If
            s = s + 14
        Next i
    End Sub
    Bill
    Use Option Explicit to avoid chasing code.

  7. #7

    Join Date
    Oct 2006
    Posts
    2,541

    Default Re: How to generate a list with the date of every other Friday.

    try
    Code:
    Sub PopulateListBox()
    Dim NextFriDay As Date, myPeirod
    myPeriod = Application.InputBox("How many periods?", type:=1)
    If myPeriod = False Then Exit Sub
    NextFriday = GetNextFriday(Date)
    For i = 0 To CInt(myPeriod) - 1
        listPayPeriodEndDates.AddItem Format$(DateAdd("d", i * 14, NextFirday), "dd/mm/yy")
    Next
    End Sub
     
    Function GetNextFriday(myDate As Date) As Date
    Dim x As Long
    x = 7 - WeekDay(myDate, 7)
    If x = 0 Then x = 7
    GetNextFriday = DateAdd("d", x, myDate)
    End Function

  8. #8
    New Member
    Join Date
    May 2009
    Posts
    4

    Default Re: How to generate a list with the date of every other Friday.

    Thanks to everyone for such quick replies!

    I'm still messing around with exactly which code I want to use, and it looks like I might use pieces of each.

    Thanks so much. When I get it completed, can I post the excel file for everyone to see?

    Thanks,

    Paul

  9. #9
    Board Regular
    Join Date
    May 2009
    Location
    Texas, USA
    Posts
    1,288

    Default Re: How to generate a list with the date of every other Friday.

    Please do
    Bill
    Use Option Explicit to avoid chasing code.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,050

    Default Re: How to generate a list with the date of every other Friday.

    TheThird

    Is the first date always 3rd March 2009?

    Does the period represent a fiscal year? Perhaps April 2009 to March/April 2010.
    Code:
    Private Sub UserForm_Initialize()
    Dim dtSt As Date
    Dim dtFn As Date
     
        dtSt = DateSerial(2009, 4, 3) ' harcoded date 3rd April 2009
        
        dtFn = DateAdd("yyyy", 1, dtSt) - 1 ' finish date 1 year on from start date
        
        While dtSt < dtFn
            ListBox1.AddItem Format(dtSt, "ddd, dd mmmm yyyy")
            dtSt = dtSt + 14
        Wend
        
    End Sub
    Bill

    03/04/2009 (3rd April 20009) is not a Wednesday in Europe or for anyone using the European date format.

    When working with dates in VBA it's sometimes an idea to use DateSerial(Year As Integer, Month As Integer, Day As Integer).

    Then hopefully days/months/whatever won't get mixed up, the correct date will be returned and it can be formatted as required.
    If posting code please use code tags.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com