date calculations - please help

[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

 

[Jeff Boyce]

Nick

Check at
www.mvps.org/access
and look for a WORKDAY() function. This may help.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Pieter Wijnen]

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