command button date

Posted on 2012-09-16
Last Modified: 2012-09-16
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
Question by:Kiwi-123
    1 Comment
    LVL 33

    Accepted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now