Get First And Last Day Of Year

vba excel date excel-2013
IcyPopTarts picture IcyPopTarts · Mar 26, 2017 · Viewed 9.9k times · Source

In VBA I know you can use this syntax to subtract a year from a date

Dim testdate As String, DateTest As String
testdate= "03/21/2017"
DateTest = Month(testdate) & "/" & Day(testdate) & "/" & Year(testdate) - 1

But how could you find the first and last date of a given year? For example, let's use the same date

testdate = "03/21/2017"

and get the following values

firstdate = "01/01/2017"
lastdate = "12/31/2017"

Answer

Fadi picture Fadi · Mar 26, 2017

You can use DateSerial:

Sub Test()

    Dim dt As Date, firstDay As Date, lastDay As Date

    dt = Date
    firstDay = DateSerial(Year(dt), 1, 1)
    lastDay = DateSerial(Year(dt), 12, 31)

    Debug.Print firstDay
    Debug.Print lastDay

End Sub

Related questions

Autofilter with column formatted as date

I am using an AutoFilter with VBA in Excel that works for regular filters, but not a column formatted as date. I can filter it manually. If I run my code, it filters nothing but when I check the filter …

Read More
How to find the difference between dates in VBA

I am trying to find out the difference between the system date and the date stored in the worksheet. If the difference between them is > 30 days, the result is true, else the result is false Dim result as boolean …

Read More
SQL Query between dates in VBA

I've a problem making a sql query in VBA using excel and an access database. In mi VBA code I've two data variables with this content: DateMin = 31/07/2014 22:00:00 DateMax = 01/08/2014 06:00:00 And I have an access database with many data with a date …

Read More