Get First And Last Day Of Year

Question

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"

I strongly urge you to consider using `Date` data types when working with dates - using `String` types will cause you so much grief in the future that it is better to change now. — YowE3K, Mar 26 '17 at 18:48
@YowE3K - the dates are being input into a user form text box. Can I have a date type for this or must it be string since it is a text box? — IcyPopTarts, Mar 27 '17 at 00:33
It probably needs to be a String in the text box, but you should convert to a `Date` as soon as possible once the user has entered it. Something like `Dim myDate As Date` `If Not IsDate(TextBox1.Text) Then MsgBox "Not a date": Exit Sub Else myDate = CDate(TextBox1.Text)`. That would allow the user to enter something like "27 March 2017" into the text box and `MyDate` would be set to the `Date` `27/03/2017` (or `03/27/2017` if you think in terms of "mm/dd/yyyy" dates) which can then be used in any date-related calculations. — YowE3K, Mar 27 '17 at 00:43

score 10 · Answer 1 · answered Mar 26 '17 at 15:40

10

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

answered Mar 26 '17 at 15:40

Fadi

3,302
3
18
41

score 2 · Accepted Answer · answered Mar 26 '17 at 15:30

If it is always the beginning and the end of the year that interest you, you can just use the 1st of January and the 31st of december. To mimic your syntax :

Dim testdate As String, DateTest As String
testdate= "03/21/2017"
FirstDayOfYear = "1/1/" & Year(testdate)
LastDayOfYear = "12/31/" & Year(testdate)

Get First And Last Day Of Year

2 Answers2