Pinterest Pixel

Top 11 Excel Date and Time Functions to Boost Your Productivity

Excel provides an extensive array of date and time functions, enabling efficient management and analysis of date... read more

Download Excel Workbook
John Michaloudis
Posted on

Overview

Top 11 Excel Date and Time Functions to Boost Your Productivity | MyExcelOnline Top 11 Excel Date and Time Functions to Boost Your Productivity | MyExcelOnline

Excel provides an extensive array of date and time functions, enabling efficient management and analysis of date and time-related data. These functions empower users to perform tasks such as finding the number of days, months, or years between dates, determining the elapsed time, extracting specific components of dates, etc.

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)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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.

Top 11 Excel Date and Time Functions to Boost Your Productivity

The correct date format will now be displayed.

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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.

Top 11 Excel Date and Time Functions to Boost Your Productivity

The correct time format will now be displayed.

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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 –

See also  Split the Date Using Power Query or Get & Transform

=TODAY()

Top 11 Excel Date and Time Functions to Boost Your Productivity

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()

Top 11 Excel Date and Time Functions to Boost Your Productivity

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)

Top 11 Excel Date and Time Functions to Boost Your Productivity

=MONTH(date)

Top 11 Excel Date and Time Functions to Boost Your Productivity

=YEAR(date)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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)

Top 11 Excel Date and Time Functions to Boost Your Productivity

=MINUTE(time)

Top 11 Excel Date and Time Functions to Boost Your Productivity

=SECOND(time)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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(

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

See also  Cleaning Data with Excel's SUBSTITUTE Formula

=EDATE(A2,

Top 11 Excel Date and Time Functions to Boost Your Productivity

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)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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.

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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(

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=EOMONTH(A2,

Top 11 Excel Date and Time Functions to Boost Your Productivity

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)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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.

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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.
See also  How to Use YYYY-MM-DD Date Format in Excel - Step by Step Guide

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(

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=DATEDIF(A2,

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=DATEDIF(A2,B2,

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=DATEDIF(A2,B2,”m”)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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.

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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(

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=WEEKDAY(A2,

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=WEEKDAY(A2,1)

Top 11 Excel Date and Time Functions to Boost Your Productivity

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).

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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.

See also  Excel´s EndOfMonth function

=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(

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=WEEKNUM(A2,

Top 11 Excel Date and Time Functions to Boost Your Productivity

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

=WEEKNUM(A2,1)

Top 11 Excel Date and Time Functions to Boost Your Productivity

You now have your week numbers!

Top 11 Excel Date and Time Functions to Boost Your Productivity

 

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.

If you like this Excel tip, please share it
Top 11 Excel Date and Time Functions to Boost Your Productivity | MyExcelOnline Top 11 Excel Date and Time Functions to Boost Your Productivity | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...