Previous Topic Next Topic

Vba excel problem

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 -
Hello,

Does anyone know the vba to do the following:

On command button select
- cell A3 = (1st of next month)
- cell A4 = (14th of the month or the next working day)

Can somebody give me an idea on how to get started??

Thanks in advance

PM

Excel 2003

1 reply

Answer 1 / 1
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
You can try this

Dim FirstOfMonth As Date
Dim FourteenOfMonth As Date

FirstOfMonth = CDate(DateSerial(Year(Date), Month(Date) + 1, 1))
FourteenOfMonth = CDate(DateSerial(Year(Date), Month(Date) + 1, 14))

' if day is a saturday, then add 2 days
If (Weekday(FourteenOfMonth, vbMonday) = 6) Then
FourteenOfMonth = DateAdd("d", 2, FourteenOfMonth)

' if day is a sunday, then add 1 day
ElseIf (Weekday(FourteenOfMonth, vbMonday) = 7) Then
FourteenOfMonth = DateAdd("d", 1, FourteenOfMonth)
End If


Cells(3, 1) = FirstOfMonth
Cells(4, 1) = FourteenOfMonth


Of course this does not take into account if here is a holiday
Ask a question