Excel DATE Function – How To Use

The DATE function is a financial analyst's best pal and categorized as a Date/Time function in Excel. The criticality of the DATE function's role is sourced from Excel's reluctance to keep the day, month, and year as a date. Instead, Excel stores them as a serial number.

Directly handing over dates as a text string may not work well for all Excel functions. So, we will supply the dates as a serial number using the DATE function to ensure that Excel and our worksheet formulas understand the instructions properly.

Excel DATE Function

Syntax for the DATE function

The syntax of Excel Date function is as follows:

=DATE(year, month, day)

Arguments:

year – This is a required argument that represents the year component in the date. Enter it as a 4 digit number; entering a single digit will result in Excel automatically adding 1900 to it.

For example, entering year as '10' will instruct the formula to interpret the year argument as '1910'.

Excel refers to your computer's date system set up to interpret the Year argument. Note that entering a negative value or a value greater than 9999 will return a #NUM! error.

month – This is a required argument that represents the month component in the date. Ideally, it must be an integer from 1 to 12 (January to December).

Alternatively, if you input a number greater than 12, Excel will count that many months starting from January of the year specified in the year argument. For example, entering year as 2001 and month as 18 will return a serial number that represents June 01, 2002. Note that Excel will return the date as the first date of the interpreted month (June 01 in our case), regardless of what you input in the date argument.

On the other hand, if you enter a value less than 1, Excel will reduce that many months from January of the year specified, plus 1. For example, if you enter month as -10 and year as 2001, Excel will return a serial number that represents February 01, 2000.

day – This is a required argument that represents the day component of the date. While in an ideal case, you will enter an integer from 1 to 31 as the date argument, it can be supplied as any other positive or negative integer and Excel will apply the same principles to compute the date as it does for the month argument.

Important characteristics of the DATE function

  • DATE function returns a #NUM! error when year argument is supplied with a negative number or a number greater than 9999.
  • DATE function returns a #VALUE! error when any of its three arguments is supplied with a non-numeric value.
  • If you see the Excel DATE function is returning a serial number like 36557 instead of a date, you will need to change the cell's format. To do this, select the cell containing the returned date and search for the 'Short Date' option in the drop-down menu of cell formatting options in the 'Number group'. These options sit on Excel's Home Tab that appears at the top left.

Examples of the DATE function

Now let's have a look at some of the examples of the DATE function in Excel.

Example 1 – The Plain – Vanilla DATE formula

In its purest form, this is what a DATE formula looks like:

=DATE(2001, 3, 15)

Excel-Date-Function_Example-001

This formula returns a serial number that corresponds to the date March 15, 2001.

However, the formula can be slightly tweaked to incorporate other date functions in Excel as well, like so:

=DATE(YEAR(TODAY()), 3, 15)

Excel-Date-Function_Example-002

This formula will fetch the serial number for the current year (and the specified month and day), and return 03/15/2021.

Much the same way, you can use TODAY for the month argument as well, like so:

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

Excel-Date-Function_Example-003

This formula will fetch the serial number for the current year as well as the current month (and the specified day), and return 02/15/2021.

Example 2 – Using Cell References in DATE function Arguments

If the values you want to supply in the arguments are stored in cells on your spreadsheet, you can reference those cells, like so:

=DATE(A2, B2, C2)

Excel-Date-Function_Example-004

With the values supplied from column A, the DATE function returns 04/30/2001.

Example 3 – Converting a number into a date using the DATE function

Let's say you scalped the internet and landed on a large data set with dates written in a format that Excel does not understand (for example, 20052005).

In that (or similar) case, we can use the following formula to help Excel make sense of the data and interpret these numbers appropriately as dates:

=DATE(RIGHT(A2, 4), MID(A2, 3, 2), LEFT(A2, 2))

Excel-Date-Function_Example-005

Recommended ReadingExcel MID function – How to use

Addition and Subtraction of Dates With DATE function

We discussed earlier how Excel store dates as serial numbers. There is of course a logical reason why Excel does this – it enables us to add or subtract a specified number of days from a given date.

To illustrate how this works, we plugged the following formula into our sheet:

=DATE(2001, 3, 15) + 20

Excel-Date-Function_Example-006

When you add a +20 at the end of this DATE formula, Excel will return the date 20 days after 03/15/2001, which is 04/04/2001.

Alternatively, to subtract a certain number of days, all you need to do is use a minus sign, like so:

=DATE(2001, 3, 15) - 10

Excel-Date-Function_Example-007

But, let's say you want to compute the number of days between today and another date. In that case, we will subtract both dates, like so:

=TODAY() - DATE(2021, 2, 20)

Excel-Date-Function_Example-008

The formula will return the number of days between today (whenever that may be) and March 15, 2001.

Changing DATE format With DATE function

So, you added some neat formulas to your Excel sheet and everything worked out fine. But, let's say you want to use different separators for the days (for example, '/' instead of '-'), or you want to use the name of the month instead of a number (for example, January instead of 01).

We discussed earlier that Excel will refer to your computer's date format and uses the same format for displaying dates in a worksheet.

To change this format, you do not need a formula. Instead, you need to tweak some settings.

Select the cell that contains the output from the DATE function. Right-click, and choose "Format Cells". You should see the following dialogue box:

Excel-Date-Function_Format-Dates-009

Choose the format of your preference and click "OK".

Conditional Formatting with DATE function

Let's say you have a large data set and you want to separate the data points occurring before and after a certain date.

To do this, we will use Excel's conditional formatting rules and instruct Excel to shade these 2 cells with 2 separate colors so it becomes easy for us to discern.

We have our dates listed out in column A, and we want to shade the dates occurring before 12/31/2012 in green, and the ones occurring after in red.

To illustrate the entire process, we will use the following formula as a starting point:

=$A2 < DATE(2012, 12, 31)

Excel-Date-Function_Format-Dates-010

Notice how this formula returns a Boolean value but does not shade any cells.

Let's move a step further and apply a shade.

Select the range A2:A8 and navigate to the "Conditional Formatting" drop-down menu in the "Styles" group under the "Home" tab, and select "Manage Rules":

Excel-Date-Function_Format-Dates-011

On the "Conditional Formatting Rules Manager" click the "New Rule" button.

Excel-Date-Function_Format-Dates-012

After clicking on the "New Rule" you will see a list of Rule Types. Scroll over to "Use a formula to determine which cells to format" and choosing it will reveal a formula box. In this box, plug in the formula we used to fetch Boolean values and select the desired formatting, like so:

Excel-Date-Function_Format-Dates-013

Click "OK" and you should return to the Conditional Formatting Rules Manager dialog box.

To shade the dates occurring after 12/21/2012 red, we will add another rule following the same process. The only difference, obviously, will be in the formula. Instead of using a '<', we will use a '>=', like so: =$A2>=DATE(2012,12,31)

Excel-Date-Function_Format-Dates-014

This is what the Rules Manager box should look like at this point. Make sure that the "Applies to" box contains the appropriate range.

Excel-Date-Function_Format-Dates-015

As your final step, click on "Apply" and watch your date list switch colors.

I hope the guide gives you a confidence boost the next time you use DATE formulas in your worksheet. Remember, practice is key. By the time you are done practicing, we will have another Excel function for you to explore.

About Ankit Kaul

Well, I am Ankit Kaul, the founder of Excel Trick. I am a die-hard fan of Microsoft Excel and have been working with spreadsheets for the past 10+ years. My only aim is to turn you guys into 'Excel Geeks'. Check out more about me here.