Operations on Date and Time
In excel, we can perform various operations on date and time using VBA. Here we will perform many of them like comparison, finding weekdays, sorting, etc.
Compare Date
Here. We will learn how to compare dates in excel. Dates are stored in excel. It will count the number of days after January 0, 1900.
- Enter the numbers in column A.
- These numbers are date, so change the column format cell to date.
- In VBA, declare the variable of type I of integer. After that, add the loop and add the following if condition to the loop to change the color of the date following the condition to red.
Code:
Dim i As Integer
For i = 1 To 5
If Cells(i, 1).Value = Date Then Cells(i, 1).Font.Color = vbRed
If Cells(i, 1).Value < DateValue("April 19, 2019") Then Cells(i, 1).Font.Color = vbRed
Next i
Result:
DateDiff Function
This function is used to get the number of days between two dates. In this, add the following code in the VBA and see the corresponding result.
Code:
Dim firstDate As Date, secondDate As Date, n As Integer
firstDate = DateValue("Jan 19, 2020")
secondDate = DateValue("Feb 25, 2020")
n = DateDiff("d", firstDate, secondDate)
MsgBox n
Result:
Explanation:
*In the above code, first, we have added two dates using the DateValue function, and in the DateDiff function, the d refers to that we need to find the number of days between the dates in the second and third argument.
You can also change d to ww to count the number of weeks between two days.
Weekdays
Now, we will write a program to find the number of weekdays between two dates. Weekdays are the days excluding Saturday and Sunday in the week.
Situation:
- First, we will declare five variables and initialize four of them.
Dim date1 As Date, date2 As Date, dateToCheck As Date
Dim daysBetween As Integer, weekdays As Integer, i As Integer
weekdays = 0
date1 = Range("B2")
date2 = Range("B3")
daysBetween = DateDiff("d", date1, date2)
- Then we will check for each date between these two, so we will use a for a loop. And add the if condition.
For i = 0 To daysBetween
If (Weekday(dateToCheck) <> 1 And Weekday(dateToCheck) <> 7) Then
weekdays = weekdays + 1
End If
- After closing the loop, we will display our required output in the MsgBox.
Next, I
MsgBox weekdays & " weekdays between these two dates."
Result:
Delay a Macro
In this example, we will learn how to deal with the macro using VBA, open a new module, and add the following code.
Sub reminder()
Application.OnTime Now() + TimeValue("00:00:03"), "reminder"
MsgBox "Don't forget your meeting at 14.30"
End Sub
- Now this will display the result after 3 seconds.
The result after 3 seconds:
- If you want to execute it at a particular time, you can add the following line:
Application.OnTime TimeValue("17:00:00 am"), "reminder"
- Now, wait till 17:00 AM, and your code will be executed then.
Year Occurences
Here we will find the occurrence of a specific year in the excel sheet.
- First, we will declare three variables. One for counting the number of years, the second for asking what year to count, and the third variable i.
Dim yearCount As Integer, yearAsk As Integer, i As Integer
- We will initialize the yearCount to 0 and yearAsk to cell C4.
yearCount = 0
yearAsk = Range("C4").Value
- Now we will apply a loop to the length of contents in the excel sheet, use an if condition and close the loop.
For i = 1 To 10
If Year(Cells(i, 1).Value) = yearAsk Then
yearCount = yearCount + 1
End If
Next i
- Now print the output in the MsgBox.
MsgBox yearCount & " occurrences in year " & yearAsk
Tasks on Schedule
In the example below, we will see the program that colors the background of the cell according to the requirement. Like below, the green color refers to the task on schedule, and the red color is for those behind schedule.
The above image 1 indicates that the task has been completed. We are checking for six June, and z is not completed on that date, so it is red.
- Open VBA creates a new module and corresponding sheet. Declare two variables of type integer and add a Do While Loop.
Dim i As Integer, j As Integer
Do While Cells(6 + i, 1).Value <> ""
i = i + 1
Loop
- Set the variable j=0 and then change the color of the completed tasks.
j = 0
Cells(6 + i, 1).Interior.ColorIndex = 4
- Add another Do While Loop, check the corresponding cells in the date and color them accordingly.
Do While Cells(4, 2 + j).Value <= Date
If Cells(6 + i, 2 + j).Value = 0 Then Cells(6 + i, 1).Interior.ColorIndex = 3
j = j + 1
Loop
Result:
Sort Birthdays
In the example below, we will look at the program that will sort birthdays into years first, followed by months, and that is followed by days.
- First, we will declare eight variables. One string variable with the name tempName, one date variable with the name tempDate. The other five variables are integer to check a month, day, next month, next day, i, j.
Dim tempDate As Date, tempName As String
Dim monthToCheck As Integer, dayToCheck As Integer, monthNext As Integer, dayNext As Integer, i As Integer, j As Integer
- Now we will start two for loops and declare 4 variables for the respective month, day, next month, next day.
For i = 2 To 13
For j = i + 1 To 13
monthToCheck = month(Cells(i, 2).Value)
dayToCheck = day(Cells(i, 2).Value)
monthNext = month(Cells(j, 2).Value)
dayNext = day(Cells(j, 2).Value)
- We compare the first date with the next date and swap accordingly to sort the date.
If (monthNext < monthToCheck) Or (monthNext = monthToCheck And dayNext < dayToCheck) Then
End If
- Now we will swap the dates and names both inside the if statement.
'swap dates
tempDate = Cells(i, 2).Value
Cells(i, 2).Value = Cells(j, 2).Value
Cells(j, 2).Value = tempDate
'swap names
tempName = Cells(i, 1).Value
Cells(i, 1).Value = Cells(j, 1).Value
Cells(j, 1).Value = tempName
- Now close both the loops.
Next j
Next i
Result:
FAQs
1. Name some data types in VBA.
Some data types in VBA are Boolean, Date, Currency, String, Integer, etc.
2. What are module-level variables?
These are the variables defined on the top of a module using the DIM statement.
3. What is the difference between Functions and Procedures?
Functions will return a value, whereas procedures or subroutines will not return a value.
4. What are macros?
It is the set of instructions stored in VBA.
Key Takeaways
In this article, we have extensively discussed the Date and Time feature in Excel, and along with that, we also discussed components of date and time in excel with all the advanced features and operations we can perform on them using VBA and to understand better we have used example in each case.
We hope that this blog has helped you enhance your knowledge of excel. If you are interested in learning about worksheets in excel, you must refer to this blog here. You will get a complete idea about worksheets in excel and workbook and the different functions we can perform on both and if you would like to learn more, check out our articles on Code studio. Do upvote our blog to help other ninjas grow.
“Happy Coding!”
309+ registered 
