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.
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.
A modal window should pop up. In this window, you can configure the default format for the date values in your spreadsheets.
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.
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.
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()+5The 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.
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.
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.
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
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
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.
