command button date

Please can you help,

I would like a command button which displays the following date:-

C2 = Monday of the current week
C3 = Friday of the current week

Then command button two

C2 = 1st Monday of the current month
C3 = Last friday of the current month
Who is Participating?
NorieConnect With a Mentor VBA ExpertCommented:
Do you mean put those values in C2 and C3?
Private Sub CommandButton1_Click()

      Range("C2").Value = Date - Weekday(Date, 3)
      Range("C3").Value = Date - Weekday(Date, 3) + 4

End Sub

Private Sub CommandButton2_Click()
Dim dtFirst As Date
Dim dtLast As Date

        dtFirst = DateSerial(Year(Date), Month(Date), 1)
        dtLast = DateSerial(Year(Date), Month(Date) + 1, 0)
       Range("C2").Value = dtFirst + 6 - Weekday(dtFirst - 1, 3)
       Range("C3").Value = dtLast + 1 - Weekday(dtLast + 1 - 6)

End Sub

Open in new window

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.