VBA Date Help

  • Thread starter Thread starter ck
  • Start date Start date
C

ck

Hello All,
I was looking for some code where I start with current day and loop through
2 years of dates. Anyways I need to build up an array of dates. I am
trying to find the 15th of each month and the last day of the month, also if
the day of the week is either Saturday or Sunday, I need the date of the
Friday before it. Does that make sense? If 10/15/03 was Saturday , I would
want to add 10/14/03 to the array. If 10/15/03 was a Sunday, I would want
10/13/03 in the array. Is this do-able? Also how do you determine the last
day of the month in VBA? This is a payroll application, where it is done
the 15th and the last day of the month and if those dates fall on a weekend,
payroll will be done the Friday before. Can anyone point me in the right
direction?

I figure loop through the dates and based on the criteria, append it to the
array. Just not sure how to set up the logic.

Thanks,
~ck
(e-mail address removed)

Remove NoSpam
 
Last Day of the month:
I haved used something similar to this:
DM = Day(MyDate) 'day of the month: 1..31
MyDate = DateAdd("d", 1-DM , MyDate) 'the first of this month
MyDate = DateAdd("m", 1 , MyDate) 'the first of next month
MyDate = DateAdd("d", -1, MyDate) 'one day back: the last of the month



You can use WeekDay(Date). It delivers 1 for Sunday ...7 for Saturday

Select case WeekDay(MyDate)
Case vbSaturday
MyDate = DateAdd("d", -1, MyDate) 'one day back to friday
Case vbSunday
MyDate = DateAdd("d", -2, MyDate) 'two days back to friday
Case else
' the day is ok
end select



Juergen
 
Replying to my own post:

I came up with this little loop. Kind of interesting, using 0 as the day
value in dateserial(2003,4,0) gives you the last day of the previous month
value. That came in kind of handy.

Dim arDates()

Public Sub MyDates()
Dim tmp As Date

ReDim arDates(0)

For myyear = 2003 To 2005
For mymonth = 1 To 12
For myday = 0 To 15 Step 15
Select Case Weekday(DateSerial(myyear, mymonth, myday))
Case 1 'Sunday
tmp = (DateSerial(myyear, mymonth, myday)) - 2
Case 7 'Saturday
tmp = (DateSerial(myyear, mymonth, myday)) - 1
Case Else
tmp = DateSerial(myyear, mymonth, myday)

End Select
'add the date to the array
If tmp > Date Then
arDates(UBound(arDates)) = tmp
ReDim Preserve arDates(UBound(arDates) + 1)
End If

Next
Next

Next

MsgBox UBound(arDates)
'loop through the array and display the dates
For j = 0 To UBound(arDates) - 1
MsgBox arDates(j)
Next

End Sub

Thanks for all the responses!!! :~)
 
Back
Top