Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can manipulate it according to the need of the user. Date and Time functions are used to interconvert date and time in different formats. In this article, we will learn about the most commonly used date and time functions.
VBA Date Functions
There are fifteen-plus different date functions in VBA, but here we will talk about some of the most commonly used date functions.
VBA Date Function
The Date() function returns the current date. The Date() function does not require any arguments. For example, declare a variable name date_1 of Date data type, call the Date() function, and store the return value in date_1, then print the date_1 in the console.
Syntax of the function: Date()
VBA DateAdd Function
The DateAdd() function is used to add an interval of date/time to the respective date or time. The function will return the resulting date or time. The function takes three arguments, Interval, Number, and Date/Time.
Syntax of the function: DateAdd(Interval, Number, Date/Time)
Interval: The first argument, represents, which part of the Date/Time, you want the interval to be added.
Types of intervals are discussed in the following table:
Intervals | Specification |
"d" | Day |
"ww" | Week |
"m" | Month |
"q" | Quarter |
"yyyy" | Year |
"y" | Day of the year |
"h" | Hour |
"n" | Minute |
"s" | Second |
Number: The second argument represents, the number of Intervals we want to add to the Date/Time.
Date/Time: The third argument represents, the Date/Time on which the changes have to occur.
For example, the current date is "20/11/2020", and we want to increase the month count by 8. Then use, DateAdd("m", 8, "20/11/2020"), and the final output date will be "20/07/2021".
VBA DateDiff Function
The DateDiff() function is used to get the difference between two dates, in terms of the year, month, day, hours, seconds, etc. The function will return the resulting Date/Time. The functions take three mandatory arguments, Interval, Date1, and Date2.
Syntax of the function: DateDiff(Interval, Date1, Date2)
Interval: The first argument, represents, the part of the Date/Time, you want to see the difference. For example, in terms of the year, day, month, etc. Refer to the table in VBADateAdd() function, for the values of Interval.
Date1: Date1 is the second argument. It tells the start date in an interval.
Date 2: Date2 is the third argument. It tells the end date in an interval.
For example, consider Date1 as "20/10/2020", and Date2 as "20/12/2021", and the difference in terms of "d"(days). The final output of the function will be 426.
VBA DatePart Function
The DatePart() function is used to get a particular part(day, week, year) from the date. The function returns the part of date in asked format by the user. The function takes two mandatory arguments, Interval, Date.
Syntax of the function: DatePart(Interval, Date)
Interval: The first argument, represents, the part of the Date/Time, one wants to see. For example, "yyyy" represents the year, and tells the DatePart function to return the year of an input Date.
Date: The second argument, which represents the date user will enter, to check a particular part of a date.
For example, consider the Date as "10/10/2020", and it has been asked to print the year of this particular date. So, we will use "yyyy" as the first argument. The final output is 2020.
VBA MonthName Function
The MonthName() function returns the name of the month according to the integer value. The function takes one mandatory argument, a number.
Syntax of the function: MonthName(number)
number: The first argument, which tells about the number of the month.
For example, 11 is the argument of the MonthName() function, the function returns "November".
VBA Time Functions
There are ten-plus different time functions in VBA, but here we will talk about some of the most commonly used time functions.
VBA Now Function
The Now() function is used to get the current date and time of the system. The function does not take any arguments. For example, declare a variable name date_2 of Date data type, call the Now() function, and store the return value in date_2, then print the date_2 in the console.
Syntax of the function: Now()
VBA Time Function
The Time() function is used to get the current time of your system. The function does not take any arguments. For example, call the Time() function, and the system will return the current time i.e. 5:20:20 PM.
Syntax of the function: Time()
VBA Hour Function
The hour() function is used to get the hour from the time. The function takes one mandatory argument, Time.
Syntax of the function: hour(Time)
Time: The first argument, which represents the time user will enter.
For example, consider the time "1:08:42 PM", and it has been asked to print the hour of this particular time. The final output will be 1.
VBA Minute Function
The minute() function is used to get the minute from the time. The function takes one mandatory argument, Time.
Syntax of the function: minute(Time)
Time: The first argument, which represents the time user will enter.
For example, consider the time "1:08:42 PM", and it has been asked to print the minute of this particular time. The final output will be 08.
VBA Second Function
The second() function is used to get the seconds from the time. The function takes one mandatory argument, Time.
Syntax of the function: second(Time)
Time: The first argument, which represents the time user will enter.
For example, consider the time "1:08:42 PM", and it has been asked to print the seconds of this particular time. The final output will be 42.
Similar Reads
Function and Sub in Excel VBA
In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-proc
4 min read
Top Excel Data Analysis Functions
Have you ever analyzed any data? What does it mean? Well, analyzing any kind of data means interpreting, collecting, transforming, cleaning, and visualizing data to discover valuable insights that drive smarter and more effective decisions related to business or anywhere you need it. So, Excel solve
6 min read
Using MONTH and EOMONTH functions in Excel
In this easy-to-follow guide, we'll explore the Excel MONTH and EOMONTH functions. These functions are super handy for working with dates. We'll show you how to use them to figure out which month a date belongs to, get the first and last day of a month, and more. Whether you're new to Excel or an ex
13 min read
How to Insert a Function in Excel?
In MS Excel formula is an expression that help to calculate the value of a cell and in Microsoft Excel has many inbuilt in functions that we can use in our formula. Now if you want to insert a function in Excel or want to see all the functions by category, then in this series of MS Excel tutorial gu
4 min read
Excel DATEDIF Function
The DATEDIF is a date function that is generally used to find the number of total days, months, and years between two dates. It takes two dates as an argument with one keyword to specify which type of difference is required as an output. The output of the function will always be an integer form. Dat
6 min read
How to Change Date Format in Excel
You know that Excel is one of the best spreadsheets you can work on. Excel is the first preference of many users to create an impressive data set. Have you tried filling in the Date on the sheet? Date with the data is a common practice done by users in Excel to get information timeline. To help orga
8 min read
Excel Date and Time Formats With Examples
Excel stores dates as serial numbers and times as fractional values of a day, which means what you see on the surface is often just a formatted version of something else entirely. If you're not familiar with how Excel handles these values, your data might display incorrectly or unexpected results in
4 min read
SQLite DATE Function
In SQLite database management the DATE() function is useful for handling date and time data very effectively. This function fasts the extraction of the date part from a datetime expression or conversion of a text representation of a date in date format. In this article, we will learn about the DATE(
4 min read
Python DateTime - strptime() Function
strptime() is another method available in DateTime which is used to format the time stamp which is in string format to date-time object.Syntax: datetime.strptime(time_data, format_data)Parameter:time_data is the time present in string formatformat_data is the data present in datetime format which is
7 min read
Power BI - DAX Date Functions
Knowing when and how to use DAX functions helps you build strong, high-performance data models and analysis large amounts of data in Power BI. In this article, weâll explore important DAX Date Functions to help you manage dates in Power BITo begin load a dataset that contains date fields. Here we ar
3 min read