Excel DATE Function (6 Practical Examples)

Excel DATE Function

Excel DATE Function creates a date from individual year, month, and day. Every argument representing year, month, and day is a number, the result is a valid Excel Date. The MS Excel stores dates as sequential serial numbers so that those dates can be used in calculations. For example, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900. You will need to change the number format (Format Cells) in order to display a proper date.

In this section:

---

1. Syntax of DATE Function
2. Example 1:Return the serial number corresponding to a date
3. Example 2: Convert Hard-coded numbers to date
4. Example 3: Find the first day of current year
5. Example 4: Find the day before a date
6. Example 5: SUMIF with a DATE condition
7. Example 6: Convert text to date

---

1. Syntax of Excel DATE Function:

DATE(year,month,day)

where:

• year: The value of the year argument, which can include one to four digits. Excel interprets the year argument according to the date system users’ computers are using. By default, Microsoft Excel for Windows uses the 1900 date system, which means the first date is January 1, 1900, and it has the serial number 1.
• month:  The value of the month argument (which ranges between 1 to 12). It can be a positive or negative integer representing the month of the year.
• day: The value of the day argument, which can be between 1 to 31. It can also be a positive or negative integer representing the day of the month.

All the arguments are REQUIRED.

2. Example 1: Return the serial number corresponding to a date:

To return the serial number of date corresponding to a date is =NUMBERVALUE(Date or reference to cell with date). To convert the date into serial number, users can also use the format option. Then they need to follow the following steps:

Step 1: Select the cell with date.

Step 2: Right click on the selected cell, and then click Format Cell.

Step 3: Now, on the format cell dialogue box, click on “General“, and click OK.

3. Convert Hard-coded numbers to date:

To convert hard coded numbers to date, the formula is =DATE(year, month, day).

4. Find the first day of current year:

To find the first day of the current year, the formula is =TEXTDATE(YEAR(TODAY()), 1,1), “dddd”).

5. Find the day before a date:

To find a day before a date, we can use the DATE and subtract the required number of days–a number. For example, we want to find the 7 day before today, then the formula is:

=DATE(2022, 7, 1)-7, which will return 06/24/2022

6. SUMIF with a DATE condition:

To SUM with a date condition, the formula is =SUMIF(date condition, SUM_RANGE). In our example, we want to find the total sales after June 11, 2022, and the formula is =SUMIF(C6:C9,”>6/11/2022″, D6:D9), which returns total sales after 6/11/2022.

7. Convert text to date:

To convert text to a date, the formula is =DATE(RIGHT(C6,4), MID(C6,3,2), LEFT(C6,2)), which convert the text value to date.

More related readings:

1. MS Post on DATE Function
2. Excel CONCAT Function
3. Extract first Name in excel (3 practical examples)
4. Extract Middle names (3 practical examples)
5. Excel ARRAYTOTEXT Function
6. Excel CLEAN Function with practical examples
7. Excel CONCAT Function to join text
8. Excel REPLACE Function to replace text
9. Excel TRIM Function with Practical Examples
10. Excel RIGHT Function
11. How to generate tomorrow’s date in Excel