Date calculations - please tell me where I am going wrong


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

D

Douglas J. Steele

How have you declared dated (and dayd)?

Assuming the first working day will be Monday if the first day of the month
is a Saturday or Sunday and that pymt1date, pymt2date are controls on your
form, try:

Dim dated As Date
Dim j As Long

For j = 1 To 6
dated = DateSerial(Year(Date), Month(Date) + j, 1)
Select Case Weekday(DateD)
Case vbSaturday
dated = DateAdd('d", 2, dated)
Case vbSunday
dated = DateAdd('d", 1, dated)
End Select
Me.Controls("pymt" & j & "date") = Format(dated, "dd/mm/yy")
Next j

DateSerial work fine, even when Month(Date) + j is greater than 12
 

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