Excel VBA, Working with Dates, Comparing, Adding, Subtracting …
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
Contents
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