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

TheThird152

New Member
Joined
May 29, 2009
Messages
4
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
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:
Upvote 0
Bill,

Could you a line after the loop, to set the Min of assigned dates, to be the default value?
 
Upvote 0
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:

<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br>    <br>    <SPAN style="color:#007F00">'// Either a list or combo box... //</SPAN><br>    Me.ListBox1.List = Dates_Return(Date)<br>    Me.ComboBox1.List = Dates_Return(<SPAN style="color:#00007F">Date</SPAN>)<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> Dates_Return(Input<SPAN style="color:#00007F">Date</SPAN> <SPAN style="color:#00007F">As</SPAN> Date) <SPAN style="color:#00007F">As</SPAN> Date()<br>Dim _<br>iCnt                <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, _<br>dtmAddToList        <SPAN style="color:#00007F">As</SPAN> Date, _<br>aryDates(1 <SPAN style="color:#00007F">To</SPAN> 6)    <SPAN style="color:#00007F">As</SPAN> Date<br><br><SPAN style="color:#00007F">Const</SPAN> DATE_INITIAL <SPAN style="color:#00007F">As</SPAN> Date = #5/15/2009#<br><br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> <SPAN style="color:#00007F">Not</SPAN> (CLng(InputDate) - CLng(DATE_INITIAL)) Mod 14 = 0<br>        InputDate = InputDate - 1<br>    <SPAN style="color:#00007F">Loop</SPAN><br>    <br>    iCnt = 0<br>    <br>    <SPAN style="color:#00007F">For</SPAN> dtmAddToList = InputDate - 14 To InputDate + 56 <SPAN style="color:#00007F">Step</SPAN> 14<br>        iCnt = iCnt + 1<br>        aryDates(iCnt) = dtmAddToList<br>    <SPAN style="color:#00007F">Next</SPAN><br>    <br>    Dates_Return = aryDates<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>

Hope that helps,

Mark
 
Last edited:
Upvote 0

listPayPeriodEndDates.AddItem "05/29/09"
listPayPeriodEndDates.AddItem "06/05/09"
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
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
 <o:p></o:p>
    First = True
    s = DateValue("05/8/09")   'prime date
 <o:p></o:p>
    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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top