Functions in Excel for Date Formulas

Introduction

Microsoft Excel provides a variety of functions that can be used to work with dates. Each function performs a simple operation; if you combine multiple functions within one formula, you can tackle more complex tasks. In this article, we'll talk about how to use functions in Excel for date formulas and provide some examples to illustrate the process.

Default date format in Excel

Different countries and regions use different date formats. Much of the world uses a DD/MM/YY format; however, the United States uses a MM/DD/YY format. If you'd like to change the default date format for your Excel spreadsheets, click on the dropdown for the More Number Formats.. option, which can be found in the Home tab.

Change the default date format in Excel and how to change the date format in Excel

A modal window should pop up. In this window, you can configure the default format for the date values in your spreadsheets.

Excel date formula

Date formulas allow you to customize the exact date values for a cell. Many date formulas make use of the DATE() function, which can be entered as part of a formula in a cell of a worksheet. This function actually returns the date’s serial number in a General numeric format, but is displayed as a date based on your chosen format.

Excel date format formula

Here's an example of the DATE() function being used as a part of a cell formula:

=DATE(2020, 2, 11)

This formula returns a serial number that corresponds to 11-February-2020 or 2/11/20, depending on your selected date format.

Formula in Excel to get the date for today

The TODAY() function is the simplest way to get the current date in an Excel spreadsheet. Just enter the following in a cell:

=TODAY()

If you use the TODAY() function call without passing any arguments to it, it will display the current date in the (MM/DD/YYYY) format.

The TODAY() function can also be used as a part of more complex formulas based on today’s date. For example, to add five days to the current date, just use the following formula:

=TODAY()+5

NOW function in Excel

The Excel NOW() function returns the current date and time. To display today’s date and current time in your worksheet, simply enter the following formula in a cell:

=NOW()

The formula example shown below will return the first day of the current year and month:

=DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

You can use the =DATE(2020, 2, 11)-5 formula to subtract five days from February 11th, 2020.

Screenshot of example functions in Excel for date formulas in a spreadsheet

Formulas in Excel to get a date value

The DATEVALUE() function converts a date in the text format to a serial number that Microsoft Excel recognizes as a date.

Let's look at a few simple DATEVALUE() function examples used in the formulas below:

=DATEVALUE("11-Feb-2020")

=DATEVALUE("2/11/2020")

=DATEVALUE("2 11, 2020")

Regardless of the date format, these values were able to be converted to a serial number.

Excel TEXT function

The TEXT() function will change date values to text strings in a variety of formats. Let's look at some examples:

=DAY(A2) - returns the day of the date in A2
=DAY(DATE(2020,2,11)) - returns the day of 11-Feb-2020
=DAY(TODAY()) - returns the day of today’s date

NOTE: If you want to extract just a specific value from a date, like the DAY(), MONTH(), or YEAR(), you'll have to change the cell's format from Date to General. This will prevent Excel from attempting to convert the numeric value to a date-like format.

Excel MONTH function

The MONTH function in Excel returns the month of a specified date. The value is returned as an integer ranging from "1" (January) to "12" (December).

Here are some examples pf the MONTH function:

=MONTH(A2) - returns the month of a date in cell A2
=MONTH(TODAY()) - returns the current month

Excel YEAR function

The YEAR Function returns a year corresponding to a given date. The value is returned as a number from 1900 to 9999. Some examples of the function are shown below:

=YEAR(A2) - returns the year of a date in cell A2
=YEAR("11-Feb-2020") - returns the year of the specified date

The following DATE() example shows a simple way to get the year of a given date:

=YEAR(DATE(2020,2,11))

=YEAR(TODAY()) - returns the current year

Screenshot of functions in Excel for date formulas to get date values

Conclusion

If you're working with data in Microsoft Excel, it's important to know how to retrieve and manipulate date values. In this article, we reviewed some common Excel date functions and showed how they can be implemented in formulas. With our explanations and examples, you'll be ready to utilize Excel date functions in your own spreadsheets.