If you’re working with dates in your excel sheets then you have to understand the date object. By using the date object you could easily process the dates on your excel sheets. You can compare dates to see which one is more recent, you can add and subtract a certain amount of days month, years to a specific date. You can get the current date ….

You can download the codes and files related to this article here.
Jump to:

• DateSerial(), Creating Date Objects From Year, Month and Day Values
• CDate(), Creating (Converting) Date Objects From Strings
• IsDate(), Checking if String is a Date Expression
• Comparing Dates, Recent, Older …
• Getting The Year, Month and Day Values From A Date Object
• Adding and Subtracting Years, Months and Days From Dates
• Date(), Getting Current Date

DateSerial(), Creating Date Objects From Year, Month and Day Values:

Using the the DateSerial() function you can create a date object by providing the year, month and day as input. The following code creates a date object for the date April/5/2014 and prints it in cell A2:

Sub Example1()

Dim objDate As Date
'year, month, day
objDate = DateSerial(2014, 4, 5)

'prints the result in cell A1 
Cells(2, 1) = objDate
End Sub


CDate(), Creating (Converting) Date Objects From Strings:

Using the CDate function you can convert a string expression to a Date object. The code below converts the string expression “1/1/2013” to a Date object and prints the results in cell B2:

Sub Example2()

Dim objDate As Date
'A string expression
objDate = CDate("1/1/2013")

'prints the result in cell B2
Cells(2, 2) = objDate
End Sub


IsDate(), Checking if String is a Date Expression:

If the input argument to the CDate() function is not a date expression, you will get an exception. In order to check if a string is a valid date expression you can use the function IsDate(). The code below checks the two strings “Not a Date” and “3/2/2012”, and determines which one is a string expression, and prints the date in cell C2:

Sub Example3()
Dim objDate As Date
Dim strNotDate As String
Dim strDate As String

strNotDate = "Not a Date"
strDate = "3/2/2012"

If IsDate(strNotDate) = True Then
    'if its a valid date expression print the results in cell C2
    Cells(2, 3) = strNotDate
Else
    'if its not a date expression show a message box
    MsgBox ("The following string" + vbCr + """" + strNotDate _
    + """" + vbCr + "is not a valid date expression")
   
End If

If IsDate(strDate) = True Then
    Cells(2, 3) = strDate
Else
    MsgBox ("The following string" + vbCr + """" + strDate + """" _
    + vbCr + "is not a valid date expression")
End If

End Sub


Comparing Dates, Recent, Older …

Once you have created the date object you can compare them using the arithmetic operators <, =, > , <=, >= to see if they are the same  or which is more recent …  The following code takes two date expressions from the cells E2 and E3. Checks if they are valid date expressions, and prints the most recent one in cell E4:

Sub Example4()
Dim objDate1 As Date
Dim objDate2 As Date

If IsDate(Cells(2, 5)) = True Then
    objDate1 = CDate(Cells(2, 5))
Else
    MsgBox ("Invalid Input")
    Exit Sub
End If

If IsDate(Cells(3, 5)) = True Then
    objDate2 = CDate(Cells(3, 5))
Else
    MsgBox ("Invalid Input")
    Exit Sub
End If

'Note the smaller date is further in the past,
'there for the larger date would be recent
If objDate1 < objDate2 Then
    Cells(4, 5) = objDate2
Else
    Cells(4, 5) = objDate1
End If

End Sub


Getting The Year, Month and Day Values From A Date Object:

Using the functions Year(), Month(), Day() you can get the year month and day values of the date object. The following example takes the date expression in cell G2 and prints the year, month and day values in cells G3, G4 and G5 respectively:

Sub Example5()
Dim objDate As Date

If IsDate(Cells(2, 7)) = True Then
    objDate = CDate(Cells(2, 7))
Else
    MsgBox ("Invalid Input")
    Exit Sub
End If

'prints the year in cell G3
Cells(3, 7) = Year(objDate)
'prints the month in cell G4
Cells(4, 7) = Month(objDate)
'prints the day in cell G5
Cells(5, 7) = Day(objDate)

End Sub


Adding and Subtracting Years, Months and Days From Dates:

Using the DateSerial() function you can add years, months and days to a date object. The code below retrieves the date at cell F2, adds 3 years, 2 month and 5 days to it and prints the result in cell F3:

Sub Example6()
'the input date object
Dim objDate As Date
'the new date object
Dim objNewDate As Date
'the year value in the input date object
Dim intYear As Integer
'the month value in the input date object
Dim intMonth As Integer
'the day value in the input date object
Dim intDay As Integer

If IsDate(Cells(2, 9)) = True Then
    objDate = CDate(Cells(2, 9))
Else
    MsgBox ("Invalid Input")
    Exit Sub
End If

intYear = Year(objDate)
intMonth = Month(objDate)
intDay = Day(objDate)

'create the new date object with +3 year, +2 months and +5 days
objNewDate = DateSerial(intYear + 3, intMonth + 2, intDay + 5)

Cells(3, 9) = objNewDate

End Sub


Date(), Getting Current Date:

The function Date() returns the current date based on the computers calendar:

Sub Example7()
Dim objDate As Date

'gets the current date based on the computers calendar
objDate = Date
MsgBox (objDate)
End Sub


You can download the codes and files related to this article here.

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website  www.software-solutions-online.com