Using the DATE function and combining with other functions in Excel

Using the DATE function and combining with other functions in Excel: Excel does not store dates and months in the format of dates. Instead, Excel stores dates as a serial number, which is the main cause of confusion. Therefore, when you need to calculate dates in Excel, the DATE function is the most basic function to use. The DATE function is used to create a date from three separate values of year, month, and day. Follow this article to learn how to use the DATE function in Excel.

1. DATE function structure

Function syntax: =DATE(year, month, day)
Where:

• year: required argument, is the year used to create the date. The year argument can range from one to four digits. By default, Excel uses the 1900 date system, which means the first day is 01/01/1900.
• month: required argument, is the month used to create the date. It must be a positive or negative integer.
• day: required argument, is the day used to create the date. It must be a positive or negative integer.

Note:

• If the year is from 0 to 1899 (including 0 and 1899), the function adds 1900 to that value to calculate the year.
• If the year is from 1900 to 9999 (including 1990 and 999), the function uses that value as the year.
• If the year is less than 0 or greater than 10000, the function will return an error value #NUM!.
• If the month is greater than 12, the function will add the larger month to the first month in the next year.
• If the month is negative, the function will subtract the reverse month from the previous year.
• If the day is greater than the number of days in the determined month, the function will add that larger number of days to the next month.
• If the day is less than 1, the function will subtract the larger number of days from the previous month.

2. How to use the DATE function

a. Use the DATE function to combine day, month, and year

For example, if we want to combine the following day, month, and year into one:

Using the DATE function and combining with other functions in Excel

Using the function structure above, we have the formula for cell D2 as follows: =DATE(C2;B2;A2)

Or we can also directly enter the number of days, months, and years into the function as follows: =DATE(20;02;2020)

Copying the above formula for the cells below, we obtain the following results:

From the obtained result, we can see that:

• In cell D3: since the year in cell C3 is 82 < 1900, the function automatically adds 1900 to become the year number 1982.
• In cell D4: since the year is 11000 > 10000, the function returns an error value of #NUM!
• In cell D5: since the month is 15 > 12, the function adds the number of months greater than to the next year of 2018, which is 2019.
• In cell D6: since the month is -4, a negative number, the function subtracts the number of months from the previous year of 2009, which is 2008.
• In cell D7: since the day is 33, greater than the number of days in May, the function adds the number of days greater than to the next month.
• In cell D8: since the number of days is -3, a negative number, the function subtracts the number of days from the previous month.

b. Use the DATE function combined with the DAY, MONTH, YEAR functions

For example, we have the initial date and then need to calculate 4 days after that date, 5 months after that date, and 3 years after that date.

By combining with the YEAR, MONTH, DAY functions, we have the formula for calculating 4 days after the initial date for cell B2 as follows:

=DATE(YEAR(A2);MONTH(A2);DAY(A2)+4)

Copy the formula for the remaining cells in column B to obtain the results:

Similarly, to calculate 5 months after the initial date, we have the formula for cell C2 as follows:

=DATE(YEAR(A2);MONTH(A2)+5;DAY(A2))

The formula to calculate 3 years after the initial date for cell D2:

=DATE(YEAR(A2)+3;MONTH(A2);DAY(A2))

Using the DATE function and combining with other functions in Excel

Thus, the above article has guided you on how to use the DATE function in Excel. Good luck!