The DATE function in Excel helps create a specific date from year, month, and day values. In this article, Lammoc will guide you through using the DATE function in both basic and advanced ways.
Nội dung
1. Introduction to the DATE Function in Excel
The DATE function in Excel helps generate a specific date from year, month, and day values. This function is particularly useful when working with time-based data such as scheduling, date-based report analysis, or time calculations.
Formula:
=DATE(year, month, day)
Parameter Explanation:
Year: Returns the year value. This value ranges from one to four digits. By default, Excel starts counting years from 1900.
- If
year
is between 0 and 1899, Excel adds the value to 1900. Example:DATE(100,7,20) = 20/07/2000
. - If
year
is between 1900 and 9999, Excel directly uses the value. Example:DATE(2017,4,25) = 25/04/2017
. - If
year
is less than 0 or greater than 9999, Excel returns the#NUM!
error.
Month: Returns the month value (1-12).
- If
month
is greater than 12, Excel adds the extra months to the next year. Example:DATE(2018,14,2) = 02/02/2019
. - If
month
is less than 1, Excel subtracts the extra months from the previous year. Example:DATE(2018,-3,2) = 02/09/2017
.
Day: Returns the day value (1-31).
- If
day
exceeds the total days in the specified month, Excel adds the extra days to the next month. Example:DATE(2018,1,35) = 04/02/2018
. - If
day
is less than 1, Excel subtracts the extra days from the previous month. Example:DATE(2018,1,-15) = 16/12/2017
.
2. Basic Usage
2.1. Creating a Basic Date
Example: Create a date for February 8, 2025.
=DATE(2025, 2, 8)
Excel returns 08/02/2025 (depending on the system’s date format).
2.2. Handling Overflow in Months and Days
Example:
=DATE(2025, 15, 10)
Explanation:
- The 15th month corresponds to March of the next year (15 – 12 = 3, adding 1 year).
- Result: 10/03/2026.
3. Advanced Applications of the DATE Function
3.1. Finding the Last Day of a Month
Problem: Determine the last day of March 2025.
=DATE(2025, 4, 1) - 1
Explanation:
DATE(2025,4,1)
generates 01/04/2025.- Subtracting 1 day results in 31/03/2025.
- Result: 31/03/2025.
3.2. Calculating a Future Date After a Specific Time
Problem: Find the date 100 days after 01/01/2025.
=DATE(2025, 1, 1) + 100
Explanation:
- Excel automatically calculates the date 100 days later.
- Result: 11/04/2025.
3.3. Calculating Age Based on Date of Birth
Problem: Find the age for someone born on 20/05/1990.
=DATEDIF(DATE(1990,5,20), TODAY(), "Y")
Explanation:
DATEDIF
calculates the complete years between the birth date and the current date.TODAY()
returns today’s date.- Result: The current age as of today.
3.4. Formatting Dates with the TEXT Function
Example:
=TEXT(DATE(2025,12,25), "dddd, dd mmmm yyyy")
Explanation:
- Displays 25/12/2025 in a text format.
- Result: Thursday, 25 December 2025.
4. Common Errors When Using the DATE Function
Error | Cause | Solution |
---|---|---|
#VALUE! |
Invalid non-integer input | Check the input values |
#NUM! |
Year is less than 1900 (Excel does not support it) | Adjust the year value |
Wrong Date | Month or day value exceeds the valid range | Check the formula inputs |
5. Conclusion
The DATE function in Excel is a powerful tool for handling date-based data. With its ability to automatically adjust overflowing months and days, along with its compatibility with functions like TEXT
, DATEDIF
, and DATE
, it provides flexibility and accuracy in time-based calculations.
Start applying the DATE function today to optimize your work!
See more: OFFSET Function In Excel (Practical Application)