CODEWITHSUNDEEP
vbaexcel
Nick Norwood
Nick Norwood

Reputation: 27

I need to make a vba that will set the endDate to the last day of the month

I have 2 cells that one is starting date assigned as startDate and another cell assigned endDate. Everytime I click on the cell a calendar pops out and I enter the date. I want the macro to change the endDate to the last day of the month automatically. So when the user goes in and enters a start date the macro will input the end date which is the last day of that month. Here is the code I have right now: 

Function dhLastDayInMonth(Optional endDate As Date = 0) As Date
    ' Return the last day in the specified month.
    If endDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dtmDate = Date
    End If
    dhLastDayInMonth = DateSerial(Year(endDate), _
     Month(dtmDate) + 1, 0)
End Function

also Here is the code for the calendar:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     'check cells for desired format to trigger the calendarfrm.show routine
     'otherwise exit the sub
    Dim DateFormats, DF
    DateFormats = Array("m/d/yy;@", "mmmm d yyyy")
    For Each DF In DateFormats
        If DF = Target.NumberFormat Then
            If CalendarFrm.HelpLabel.Caption <> "" Then
                CalendarFrm.Height = 191 + CalendarFrm.HelpLabel.Height
            Else: CalendarFrm.Height = 191
                CalendarFrm.Show
            End If
        End If
    Next
End Sub

Upvotes: 0

Views: 647

Answers (3)

Chefvad
Chefvad

Reputation: 13

 EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)

This vba function will also return the last day of the month for the present month. Change the Date if a different month is needed.

Upvotes: 0

Sorceri
Sorceri

Reputation: 8033

If you are really keen on using your own VBA function then you can create a function such as this which basically gets the first day of the next month and subtracts 1 day from it to get the last day of the month. I think Thunderframe's answer is best though as it is simple and uses the native functions of excel.

Public Function dhLastDayInMonth(Optional endDate As Date = 0) As Date
    Dim dt As Date
    'check if we have a date
    If endDate = 0 Then
        ' Did the caller pass in a date? If not, use
        ' the current date.
        dt = DateTime.Now
    Else
        dt = endDate
    End If
    'check for December
    If DatePart("m", dt) = 12 Then
        'add one to the year and month
        dt = DateSerial(DatePart("yyyy", DateAdd("yyyy", 1, dt)), DatePart("m", DateAdd("m", 1, dt)), 1)
    Else
        'add 1 to the month
        dt = DateSerial(DatePart("yyyy", dt), DatePart("m", DateAdd("m", 1, dt)), 1)
    End If
    'subtract 1 day from the date to get the last date of the month
    dt = DateAdd("d", -1, dt)
    'return the end of the month
    dhLastDayInMonth = dt
End Function

Upvotes: 0

ThunderFrame
ThunderFrame

Reputation: 9461

In your endDate cell, you can use this formula:

=DATE(YEAR(startDate),MONTH(startDate)+1,0)

Where startDate is a named reference to your startDate cell.

Upvotes: 2

Related Questions