Top 11 Excel Date and Time Functions to Boost Your Productivity

Before we dive into Excel’s date and time functions, it is crucial to grasp a fundamental aspect related to it. Excel stores date and time as numbers, where –

• The integer portion is the number of days past the base date i.e. January 1, 1900.
• The decimal portion is the time of the day.

In this article, we will be covering the top 11 Excel Date and Time functions –

Download the Excel Workbook below to follow along  –
download excel workbookDate-and-Time-Functions-in-Excel.xlsx

 

1. DATE Function

It is used to create a date by specifying the following arguments – day, month, and year. You need to enter the year in four digits, month as a number between 1 to 12, and day as a number from 1 to 31.

=DATE(year,month,day)

Excel will display the date as a serial number based on the year, month, and day mentioned. January 1, 1900, is serial number 1, and August 18, 2003, is 37820 as it is 37820 days after January 1, 1900.

To change the format, go to Home > Under Number group > Short Date.

The correct date format will now be displayed.

 

2. TIME Function

It is used to create a time value by specifying the following arguments – hour, minute, and second. You need to enter the hour as a number between 0 to 23, minute as a number between 0 to 60, and second as a number from 0 to 60.

=TIME(hour,minute,second)

Time is represented as decimal in Excel. For example, 0.5 is actually 12:00:00 PM as it is half of a day. To change the format, go to Home > Under Number group > Time.

The correct time format will now be displayed.

 

3. TODAY Function

It is a dynamic function in Excel that is used to display the current date. The date will get refreshed each time you open the worksheet. To use this function, simply enter –

=TODAY()

It has no argument, you just need to enter the function with open and closed parenthesis.

 

4. NOW Function

It is a dynamic function in Excel that is used to display the current date and time. The date will get refreshed each time you open the worksheet. To use this function, simply enter –

=NOW()

It has no argument, you just need to enter the function with open and closed parenthesis.

 

5. DAY, MONTH and YEAR Functions

These functions extract the day, month, or year component from a given date value. DAY function extracts the day, the MONTH function extracts the month, and the YEAR function extracts the year.

=DAY(date)

=MONTH(date)

=YEAR(date)

These functions can be combined with other Excel functions for complex calculations and data analysis.

 

6. HOUR, MINUTE, and SECOND Functions

These functions extract the hour, minute, or second component from a given time value. HOUR function extracts the hour, the MINUTE function extracts the minute, and the SECOND function extracts the second.

=HOUR(time)

=MINUTE(time)

=SECOND(time)

These functions can be combined with other Excel functions for complex time-related calculations and analysis.

 

7. EDATE Function

EDATE function stands for the End Date and is used to add or subtract a specified number of months from a given date. It is particularly useful when you need to calculate future or past dates based on a starting point. The syntax of the EDATE function is –

=EDATE(start_date, months)

• start_date is the initial date from which you want to calculate the new date
• months is the number of months that you want to add or subtract. If you enter a positive number, months will be added to the start date or else it will be subtracted.

It is a very useful function that can be applied to calculate project timelines, work on cash flow projections, calculate payment due dates, etc. Follow the steps below to understand how to use this function –

STEP 1: Enter the EDATE function

=EDATE(

STEP 2: Enter the first argument i.e. start date. Here, the start date is mentioned in cell A2.

=EDATE(A2,

STEP 3: Enter the second argument i.e. months. Here, the number of months that needs to be added or subtracted is mentioned in cell B2.

=EDATE(A2,B2)

Below you can see that the end dates have been calculated. In 1st example, 2 months have been added to the start date i.e. 4th July 2022 and the result i.e. 4th September 2023 has been displayed in cell C2.

 

8. EOMONTH Function

EOMONTH function in Excel returns the last day of the month before or after a specified number of months of a specified date. It is often used to determine the end date of a particular month. The syntax of this function is –

=EOMONTH(start_date, months)

• start_date is the initial date from which you want to calculate the new date
• months is the number of months that you want to add or subtract. If you enter a positive number, months will be added to the start date or else it will be subtracted.

The difference between EDATE and EOMONTH is that EDATE keeps the day component of the date intact whereas EOMONTH provided the last day of the date as the result. Let’s look at an example to understand better.

STEP 1: Enter the EOMONTH function

=EOMONTH(

STEP 2: Enter the first argument i.e. start date. Here, the start date is mentioned in cell A2.

=EOMONTH(A2,

STEP 3: Enter the second argument i.e. months. Here, the number of months that needs to be added or subtracted is mentioned in cell B2.

=EOMONTH(A2,B2)

Below you can see that the last day of the end dates have been calculated. In 1st example, 2 months have been added to the start date i.e. 4th July 2022 and the last day of the resultant month i.e. 30th September 2023 has been displayed in cell C2.

 

9. DATEDIF Function

The DATEDIF function in Excel allows you to calculate the duration in days, months, or years between two given dates. Here’s how the formula works:

=DATEDIF(Start Date, End Date, Interval)

where Interval can be:

• “m” for months, or “ym” for months excluding years.
• “d” for days, or “yd” for days excluding years.
• “y” for years, or “md” for days excluding years and months.

It can be used to determine your age in days, months, or years since your birth or to calculate durations for project start and end dates.

Let’s look at an example to understand better.

STEP 1: Enter the DATEDIF function

=DATEDIF(

STEP 2: Enter the first argument i.e. start date. Here, it is mentioned in cell A2.

=DATEDIF(A2,

STEP 3: Enter the second argument i.e. end date. Here, it is mentioned in cell B2.

=DATEDIF(A2,B2,

STEP 4: Enter the third argument i.e. interval. Here, it is “m” for months.

=DATEDIF(A2,B2,”m”)

The DATEDIF function will calculate the number of months between the start and end dates specified in columns A and B respectively and display the result in column C.

 

10. WEEKDAY Function

The WEEKDAY function returns the day of the week corresponding to a given date.  The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.

=WEEKDAY(date, [return_type])

• date is the date for which you want to determine the day of the week. Required.
• return_type is used to specify the numbering system for this function. Optional.
  • 1 – Numbers 1 (Sunday) through 7 (Saturday)
  • 2 – Numbers 1 (Monday) through 7 (Sunday)
  • 3 – Numbers 0 (Monday) through 6 (Sunday)
  • 11 -Numbers 1 (Monday) through 7 (Sunday)
  • 12 – Numbers 1 (Tuesday) through 7 (Monday)
  • 13 – Numbers 1 (Wednesday) through 7 (Tuesday)
  • 14 – Numbers 1 (Thursday) through 7 (Wednesday)
  • 15 – Numbers 1 (Friday) through 7 (Thursday)
  • 16 – Numbers 1 (Saturday) through 7 (Friday)
  • 17 – Numbers 1 (Sunday) through 7 (Saturday)

So if you want to find out on what day you were born, then the WEEKDAY function will remind you. Let’s use an example to understand better.

STEP 1: Enter the WEEKDAY function

=WEEKDAY(

STEP 2: Enter the first argument i.e. date. Here, it is mentioned in cell A2.

=WEEKDAY(A2,

STEP 3: Enter the second argument i.e. return_type. Here, it is 1.

=WEEKDAY(A2,1)

In this case, the result is 3, indicating that it is a Tuesday. In Type 1, the numbering system used for days ranges from 1 (Sunday) to 7 (Saturday).

 

11. WEEKNUM Function

The WEEKNUM function in Excel calculates and returns the week number for a given date. It assigns a specific number to each week based on the calendar year. For instance, the week that includes January 1 is considered the first week of the year and is labeled as week 1.

=WEEKNUM(date,[return_type])

• date is the date for which you want to determine the week number. Required.
• return_type is used to determine on which day the week begins. Optional.
  • 1 (default) – Sunday
  • 2 – Monday
  • 11 – Monday
  • 12 – Tuesday
  • 13 – Wednesday
  • 14 – Thursday
  • 15 – Friday
  • 16 – Saturday
  • 17 – Sunday
  • 21 – Monday, the difference here is it uses the European week numbering system, week 1 is the week containing the first Thursday of the year as specified in ISO 8601

Follow the steps below to understand how the WEEKNUM function works –

STEP 1: Enter the WEEKNUM function

=WEEKNUM(

STEP 2: Enter the first argument i.e. date. Here, it is mentioned in cell A2.

=WEEKNUM(A2,

STEP 3: Enter the second argument i.e. return_type. Here, it is 1.

=WEEKNUM(A2,1)

You now have your week numbers!

 

This article provides an overview of Excel’s date and time functions, which allow users to efficiently manage and analyze date and time-related data. Each function is explained with its syntax and examples of usage. By utilizing these functions, Excel users can effectively perform various date and time-related calculations and analyses.