Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Working with Date and Time
2.1.
Components of the Date
2.2.
DateAdd
2.3.
Current Date and Time
2.4.
Components of Time
2.5.
TimeValue
3.
Operations on Date and Time
3.1.
Compare Date
3.2.
DateDiff Function
3.3.
Weekdays
3.4.
Delay a Macro
3.5.
Year Occurences
3.6.
Tasks on Schedule
3.7.
Sort Birthdays
4.
FAQs
5.
Key Takeaways
Last Updated: Mar 27, 2024

Date and Time in Excel

Author Naman Kukreja
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

Today’s world is all about data, and one who knows how to handle data always has the edge over others who don’t. One who started working with computers in school or by himself must come across excel. 

As you read this blog, you must be familiar with the advantages and the necessity of excel in dealing with data. We will become familiar with its one more feature, i.e., date and time. It is not a regular date and time. We will learn much more than just name in it.

Like different features, we can perform with them in Excel. For better, deep, and more advanced understanding, we will use Visual Basic for the Application, and in that, we will write proper code to perform functions from the backend.

So without wasting any further time, let's get on with our topic.

Working with Date and Time

Here, we will learn to work with date and time at a basic level. We will enhance the level gradually while moving further with the blog. We will write code in VBA in performing different operations.

Components of the Date

Here, we will use the macro to access the year of the given date.

In VBA, we will use the dim statement to declare and the DateValue function to initialize the date.

Code:

Dim exampleDate As Date

 
exampleDate = DateValue("22 Sep 2020")

 
MsgBox Year(exampleDate)

Result:

Similarly, we can get the day and month of a specific date.

DateAdd

Sometimes, we need to add some days to our date and want to get the corresponding result. The DateAdd function contains three arguments. The first argument, "d," refers to the days. The second argument refers to the number of days we want to add to date. The last argument refers to the date we want to perform the earlier two statements.

Code:

Dim firstDate As Date, secondDate As Date

 
firstDate = DateValue("23 Dec 2021")
secondDate = DateAdd("d", 10, firstDate)

 
MsgBox secondDate

Result:

If you want to add in months, you can change d with m.

Current Date and Time

We will use the now function to get the current date and time.

Code:

MsgBox Now

Result:

Components of Time

We can access some particular components of the date like hours, minutes, seconds. Here we will access the hour.

Code:

MsgBox Hour(Now)

Result:

Similarly, we can get minutes and seconds.

TimeValue

This will convert a string into a time serial number, i.e., 0 or 1, not in decimal like 0.6.

Code:

Dim y As Double
y = TimeValue("09:20:01")
MsgBox y

Result:

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

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!”

Live masterclass