Date Function: A Brief

The ExcelDATE function helps create a valid date using separate values for theyear,month, andday. This function is useful when you need dates that update automatically based on other values in your worksheet.

Date Function: A Syntax

=DATE(year,month,day)

1. Year– Years mentioned in as Numbers.
2. Month– Months mentioned in as Numbers.
3. Day– Days mentioned in as Numbers.

Using Date Function with Practical Examples:

TheDATE function in Excel creates a date using separate numbers for the year, month, and day. It returns a serial number that represents a valid date in Excel. To see the output as a proper date, apply a date format.

Using theDATE function is the most reliable way to create dates in Excel formulas. Unlike text-based dates, which can be misinterpreted, this function ensures that the year, month, and day values are clear and accurate.

Example 1: Hand Typed Dates as Numbers:

This is the basic and most common method to use the date function to display the proper excel date in the target cell based in the user provided numeric inputs. Refer to the below examples:

=DATE(1992,12,22)  //Displays- 22 December 1992=DATE(2012,4,14)   //Displays- 14 April 2012=DATE(2018,5,19)   //Displays- 19 May 2018Choose the target cell, type the above syntax and hit Enter button to display results.

Date Function Example displaying dates from hand typed numeric input data

Example 2: Using Cell Reference to Execute Date Function:

TheDATE function is helpful for creating dates that update automatically based on other values in a worksheet. For example, if2023 is entered in cellD1, the formula below will return the dateJuly 20, 2023:

Note: IfD1 is later changed to2025 and theDATE function will update the result to showJuly 20, 2025.

=DATE(D16,7,20)  //Displays- 20 July 2023Here- D16 represents the value located in the cell D16.

Date Function example using cell reference

In Date Functions, output year value changes with cell reference value in real time

Example 3: Using Date Function to display the First Day of the Present Year:

You can simply use the date function to display the first day of the present or current year and the formula to do this is,

=DATE(YEAR(TODAY()),1,1) // Displays- Present Year's 1st day date

The simple formula display the first day of the present year using date function.

Here to achieve this, I have used two more functions and they are: Year and Today functions.

• Year Function– Displays theyear section in the date with4 digits.
• Today Function– Display the today’s date or current day date in the cell.

This is an live example ofnesting multiple functions in Excel. TheTODAY function gets thecurrent date and passes it to theYEAR function. TheYEAR functionextracts the year and sends it to theDATE function as the year value. The month and day are set to1, so the result is the first day of the current year, such as“January 1, 2025”.

Example 4: Combining with multiple complex functions such as Date Functions with SUMIFS, COUNTIFS:

You have a dataset containing sales records, where:Column A containssales dates,Column B containssales amounts,Column C containssalesperson names. If you want to find the following: Count the number of sales transactions after a specific date (e.g., January 1, 2023), Sum the total sales after that same date, Apply both conditions dynamically based on the year, month, and day values stored in separate cells.

Formula to Count Sales After February 1, 2024:

=COUNTIF(A16:A21, ">" & DATE(2024,2,1))

Formula to Sum Sales After February 1, 2024:

=SUMIFS(B16:B21, A16:A21, ">" & DATE(2024,2,1))

Practical Example of using Date function with COUNTIF and SUM function in excel.

Verifying Answers from the above example manually: Count Explanation, Dates after February 1, 2024:

• 10-Feb-24
• 15-Mar-24
• 20-Mar-24
• 25-Apr-24

Total count =4, Which is correct one.

The SUMIFS result from the above formula (2600) is also correct because the sum of sales after Feb 1, 2024 is:

• 700 (Feb 10)
• 900 (Mar 15)
• 400 (Mar 20)
• 600 (Apr 25)

Adding all the value to get2600 which is absolutely correct. Hence the above formula is proved correctly. In this way, you can use the Date function to make work with other complex functions to solve day to day needs.

Interesting Tips about Date Function:

• The DATE function only accepts numeric values. If you enter text, it will return a#VALUE! error.
• The DATE function returns aserial number that represents avalid date in Excel.
• Excel dates start from the year1990. If you enter a year between0 and 1990, Excel will add1990 to it.

That’s it.

–Vigneshwaran Vijayakumar.

Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on ourhomepage.

References:

• Date Function byCFI Team, Retrieved on 08/03/2025:Link
• How to use the Excel DATE function byChris Menard, Retrieved on 08/03/2025:Link
• Date Function byMicrosoft Support Team, Retrieved on 08/03/2025:Link
• Date Functions and Formulas in Excel byTechnology for Teachers and Students, Retrieved on 08/03/25:Link
• How to Use Date Formulas in Excel byCustom Guide Team, Retrieved on 08/03/2025:Link