date calculations - please help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello All

I wonder if anyone can help.

I am trying to add some code to a form to calculate the first working day of
each month for the next six months...

here's how far I have got...

For j = 1 To 6

dayd = Format(1, "dd")
monthd = Month(Now) + j

If monthd > 12 Then
monthd = Format(Month(Now) + j - 12, "mm")
yeard = Format(Year(Now) + 1, "yyyy")
Else
monthd = Format(Month(Now) + j, "mm")
yeard = Format(Year(Now), "mm")
End If

dated = dayd & "/" & monthd & "/" & yeard

If Weekday(dated) = 1 Then
dayd = Format(2, "dd")
dated = dayd & "/" & monthd & "/" & yeard
End If

If Weekday(dated) = 7 Then
dayd = Format(3, "dd")
dated = dayd & "/" & monthd & "/" & yeard
End If

If j = 1 Then
pymt1date = Format(dated, "DD/MM/YYYY")
End If

If j = 2 Then
pymt2date = Format(dated, "DD/MM/YYYY")
End If

If j = 3 Then
pymt3date = Format(dated, "DD/MM/YYYY")
End If

If j = 4 Then
pymt4date = Format(dated, "DD/MM/YYYY")
End If

If j = 5 Then
pymt5date = Format(dated, "DD/MM/YYYY")
End If

If j = 6 Then
pymt6date = Format(dated, "DD/MM/YYYY")
End If

Next j

However this doesn't seem to work!

Can anyone suggest any better way of doing this please?

Any comments would be much appreciated.

Many thanks in advance and kind regards

Nick
 
Try

Public Function FirstMonday(ByVal moffset As Long) As Date
Dim d As Date
d = VBA.DateSerial(VBA.Year(VBA.Now()), VBA.Month(VBA.Now()) + moffset, 1)
If VBA.WeekDay(d, VBA.vbMonday) <> 1 Then
d = d + 8 - VBA.WeekDay(d, VBA.vbMonday)
End If
FirstMonday = d
End Function

HtH

Pieter
 
Back
Top