date calculations - please help


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
 
Ad

Advertisements

Ad

Advertisements

P

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top