DATE Function

What is the DATE Function?

The DATE function in excel allows users to calculate and create dates using different formats from data provided. The DATE function is an Excel function that combines three separate values (year, month and day) to form the date. When used along with other Excel functions, it can be used to perform a wide range of tasks related to dates, including returning specified dates.

Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1^st 1900 is serial number 1,  January 2^nd1900 is serial number 2 and so on.

The function is useful when dates are to be used in other functions such as SUMIF or COUNTIF.

How to Use the Date Function in Excel

The syntax for the DATE function is shown as:

=DATE(year, month, day)

The syntax includes the following required arguments:

• Year represents years whose value can range from a single digit (e.g. 2) to four digits (e.g., 1902)
• Month represents months of a year whose value can range from 1 to 12 starting from January
• For example, if we enter 2 as an argument, the function will return the month of February
• Day is the day of the month whose value can range from 1 to 31, representing the number of days in a month

Microsoft Excel for Windows uses the 1900 date system as a default. So, if you enter a single digit ‘2’ in the year field, Excel will assume the year to be 1902. It is better to enter 4-digit years to avoid confusion.

We can also enter a negative integer as Month. For example, if we enter DATE (2025,-2,2) as arguments, the function will subtract 2 months from the first month of 2025 to give the serial number of November 2^nd, 2024.

Similar to month, Day can also be negative. For example, if we enter (2024,10,-2) as the day, it will subtract 2 days from the 1st day of October and show the date as September 28th, 2024.

• When using this function, it is worth noting that the #NUM! error is caused if the year argument is <0 or >=10000.
• A #VALUE! error is caused if non-numeric arguments are entered.

Important Points About the DATE Function

• If the year argument is less than 0 or greater than/equal to 10000, it results in a #NUM! error
• If a non-numeric argument is entered, it leads to a #VALUE! error
• By default, Excel stores dates as sequential numbers, starting from 01-01-1900 (which is 1 for Excel). The cell in which this function is entered needs to be set to the date format. This can be done by selecting the Date dropdown in the Number group (part of the Home tab). You can also change the format to date using the format cells option.

Example 1 – DATE function

There are many ways that the DATE function can be used. Here is a simple example to show how a date can be created using the DATE excel function formula to collect the information and display it in a date format.

Using the information given below, combine the year, month, and day to form a date.

The dates can be created in a single cell using the DATE function as shown below:

Download the free Excel file to try this free Date function template and many others.

Example 2 – Adding to the DATE function

In this next example, we have been asked to add 15 days to each of the DATE function results in the previous example.

To create an appropriate formula for this, we simply need to add ‘+15’ to the previous formula.

Note, it is always best practice to reference other cells in formulas instead of hardcoding or simply typing in dates. This allows analysts to update all information and formulas quickly and reduces the likelihood of human error in the analysis. It is always important to check the formula carefully and ensure it is appropriate and fit for purpose.

In this example, the dates have now been updated to 15 days after the dates calculated using the original arguments. This has been done by linking to a master cell with the additional dates in meaning it can be easier to update this rather than having up manually update each formula from having “+15” in it. Details of this can be found in the free downloadable Financial Edge DATE function template.

We can also add years and months to any of the dates. For example, if we want to calculate the 10th anniversary of each of these dates, we will add 10 to the year argument while leaving the other arguments unchanged.

Example 3: Using DATE function with COUNTIF

We can also use the DATE function along with other Excel functions. A good example of this is to try and separate dates falling before or after an event.

Let’s now calculate the number of dates that fall after December 31st, 2022 from the example above.

Here we will be using the COUNTIF function, which counts the number of cells that meet a specified condition along with the DATE function.

As a first step, we use the DATE function to calculate the serial number that corresponds to December 31^st, 2022. This step gives us the flexibility to easily change this date without needing to overwrite any formulas.

Next, we use the COUNTIF function by selecting the reference date range (B19:B23 shown below) and ask it simply to count the number of cells with dates in that are greater than the date in cell E29.

We would write this as: =COUNTIF(B19:B23,”>”&E29)

Using the DATE and COUNTIF functions we can see that there are 3 dates in the range after  December 31^st 2022. This step gives us the flexibility to easily change this date without needing to overwrite any formulas.

Next, we use the COUNTIF function by selecting the reference date range (B19:B23 shown below) and ask it simply to count the number of cells with dates in that are greater than the date in cell E29.

Excel TODAY Function

The TODAY function in Excel returns the current date. The format of the date can be amended later using the format cells, date function to customize it.

This can be written as: =TODAY()

This function is useful when downloading data with a live feed or link in (such as Bloomberg pricing) as the TODAY function will display the data with today’s date.

It is important to note though that the live feeds may not be automatically linked to the TODAY function. For example, if the markets were closed for Thanksgiving, the TODAY function may provide the date (Thanksgiving), but the live feed data may be from the previous day as the markets are closed.

Excel DATEVALUE Function

The DATEVALUE function in Excel converts a date in the form of text to a serial number that Excel recognizes as a date. This is useful when you have dates stored as text and need to perform date calculations.

The is written as: =DATEVALUE(date_text)

This is where date_text is the text that represents a date. If we had the date 12.12.2025 in excel cell B4, we could write =DATEVALUE(B4) and it would produce the serial number 46003.

How to Correct a #VALUE! Error in the DATEVALUE Function

The #VALUE! error in the DATEVALUE function is caused if non-numeric arguments are entered. Here are some examples of possible issues and their solutions:

The date_text argument has an invalid value

The date_text argument has to be a valid text value, and not a number or a date. For example, 29 June 2025 is a valid value, but this is not 2025 June 29. To resolve this right-click on the cell and click Format Cells (or CTRL+1) and make sure the cell follows the Text format. If the value already contains text, make sure it follows a correct format.

The date is not between January 1, 1990 and December 31, 9999

Make sure that the date_text argument represents a date between January 1, 1990 and December 31, 9999.

Excel TEXT Function

The TEXT function in Excel is used to convert a number to text in a specified format. This can be useful for formatting dates, numbers, and other values.

This is written as: =TEXT(value, format_text)

This is where the value is the number to be converted, and format_text is the format you want to apply.

Excel DAY Function

The DAY function in Excel returns the day of the month from a given date.

This is written as:=DAY(date) where date is the date from which you want to extract the day.

For example, if we had the date 3.31.2024, this function would return “31” to show the number of days in the month. (Always be careful to check the excel settings to make sure it is showing the appropriate date format for your region.)

Excel MONTH Function

The MONTH function in Excel returns the month from a given date.

The syntax is: =MONTH(date) where date is the date from which you want to extract the month.

Again if we had the date 3.31.2024, this would return “3” to show it was the third month of the year.

Excel YEARFRAC function

The YEARFRAC function calculates the number of whole days between two dates and displays it as a fraction of the year.

This establishes the exact number of days between two transactions and can be extremely helpful for calculations looking at accrued interest rate payments etc. =YEARFRAC(start_date,end_date,[basis]).

The ‘basis’ element of the formula specifies which type of day count to use (such as 360 or 365 day convention). When typing the formula into excel, it should automatically display the options for the basis element of the formula.

In the example shown, we are calculating what fraction of the year is between January 1^st 2022 and March 3^rd 2022. The formula is based on actual dates in the actual 2022 year, so 31 days in January, 28 in February and 3 in March 2022.

Some calculations for bond pricing would require different bases such as actual/360 (which would be designated with the “2” basis. Other basis options are shown above.

Conclusion

The DATE function in Excel is a versatile tool that allows for the creation and manipulation of date serial numbers, which are essential for a wide range of calculations and tasks. It’s a key function in financial modeling and can be combined with other functions for more complex operations. Understanding and correctly using the DATE function can greatly enhance efficiency and accuracy in handling date-related data in Excel.