Rifat Hassan
The DATE function of Excel has a lot of use. We can use the function in many operations related to date. In this article, I will show the use of the DATE function with 10 ideal examples.
Download Practice Workbook
You can download the practice workbook from here.
Introduction to DATE Function
- Function Objective:
The DATE function is used to create a date from numeric values in the arguments.
- Syntax:
=DATE(year,month,day)- Arguments Explanation:
| ARGUMENT | REQUIRED/OPTIONAL | EXPLANATION |
| year | Required | The numeric value of year. |
| month | Required | The numeric value of month. |
| day | Required | The numeric value of day. |
- Return Parameter:
A date containing year, month, and day.
- Version:
The DATE function has been introduced in Excel 2007 and available for all versions after that.
DATE Function with Number in Argument
We can use plane numbers as the arguments of the DATE function. I am showing the steps for using the DATE function with plane numbers as arguments below.
- Simply, put the number that represents the year, month, and day in the arguments of the DATE function and instantly we will see the corresponding date in the cell.
- For example, in Cell C5 I wrote the following formula.
=DATE(2022,5,20)- And the formula gives the output date 20-May-2022.
- I have included more dates in a similar manner.
DATE Function with Cell Reference in Argument
Instead of inserting plane number, we can also insert cell references as arguments of the DATE function. For this use of the DATE function, we need a cell in the sheet that contains the numeric value of day, month or year. In the following example, I have included a column for Day values.
- In Cell D5, write the following formula:
=DATE(2022,5,C5)The formula takes the value of Cell C5 as the Day argument.
- And we can see the corresponding date in Cell D5.
- In a similar way, I have included more dates in the sheet.
DATE Function with Arrays
The DATE function can take arrays as the argument also. In the worksheet, I have taken some numeric values in the Day column. Let’s use the DATE function with the array in the range C5:C15.
- Write the following formula in Cell D5:
=DATE(2022,5,C5:C15) In the formula, I just imputed an array instead of a specific cell which gives an output array of dates.
10 Ideal Examples to Use Exel DATE Function
There are various uses of the DATE function in Excel. Here, I will discuss the procedure to use Excel DATE function with 10 ideal examples.
1. Find First Day of Month Using DATE Function
We can find the first day or last day of a month using the DATE function. Let’s follow the steps given below for that.
- In Cell D5, write the following formula:
=C5-DAY(DATE(2022,9,15))+1
- Of course, you can find out the dates for other candidates in a similar fashion.
Read More: How to Use DATEDIF Function in Excel (6 Suitable Examples)
2. Insert Last Day of Month with EOMONTH and DATE Functions
Now, it’s time to find out the last day of the month. The procedure is described below.
- Simply, write the following formula in Cell D5 and hit Enter.
=EOMONTH(DATE(2022,2,12),0)
Read More: How to Use Excel MONTH Function (6 Examples)
3. Apply Excel DATE Function to Add Specific Days to Date
In this example, we will add specific days to a date using the DATE function. Let’s observe the steps below.
- Firstly, type the formula in Cell D5.
=DATE(2022,2,12)+265- After that, press Enter.
- The formula will return the date after adding 265 to the date from DATE function.
Read More: How to Use DAYS Function in Excel (7 Examples)
4. Use DATE Function to Subtract Specific Days from Date
We can also subtract specific days from a date by using the DATE function. Let’s pay attention to the steps below.
- Write the following formula in Cell D5 and press Enter.
=DATE(2022,2,12)-265In the formula, we simply subtracted 265 from the date output of the DATE function.
- Consecutively, the formula returns the date which is back to 265 days.
- Surely, we can subtract any number instead of 265 in the formula.
5. Identify First Day of Year with DATE and YEAR Functions
Another use of the DATE function is identifying the first date of the year. I am showing the procedure for that in the following section.
- In the first place, write the following formula in Cell D5 and press Enter.
=DATE(YEAR(C5),1,1)- Instantly, we will see the 1st date of the year as result.
6. Count Days Between Two Dates in Excel
We can also calculate the difference between two dates using the DATE function. Let’s follow the steps below for that.
- Simply, write the following formula in Cell E5.
=DATE(2022,11,2)-DATE(2022,9,3)- Now, hit Enter.
- The formula will give the difference between two dates which are the output of two DATE functions.
7. Count Total Workdays Between Two Dates in Excel
We can find the net workdays using the NETWORKDAYS function of Excel. To count the total number of workdays between two days, we can use the NETWORKDAYS function.
The syntax of this function is:
=NETWORKDAYS(start_date,end_date,[holidays])Here, start_date is the starting date, end_date is the ending date and [holidays] is a list of holidays.
Let’s observe the steps below to find the workdays between 1-Jan-2020 and 3-Nov-2022.
- Type the following formula in Cell E5 and hit Enter.
=NETWORKDAYS(DATE(2020,1,1),DATE(2022,11,3),B5:B15)- As a result, we will see the net workdays between the two dates excluding the holidays also.
Read More: How to Use the Excel DAYS Function with a Practical Example
Similar Readings
- How to Auto-Update Current Time in Excel (With Formula and VBA)
- Remove Time from Date in Excel (6 Approaches)
- How to Use WEEKNUM Function in Excel (2 Ways)
- Use SECOND Function in Excel (3 Suitable Examples)
- How to Use MINUTE Function in Excel (6 Suitable Examples)
8. Find Workday after Specific Number of Days in Excel
If you want you can also perform the reverse work of the NETWORKDAYS function using the WORKDAY function.
That means you can find out a specific date after a given number of workdays from a starting date using this function.
The Syntax of the WORKDAY function is:
=WORKDAY(start_date,days,[holidays])To find the workday after 1000 days starting from 1-January-2020, with B5:B15 as a list of holidays, follow the steps below:
- Firstly, select Cell E5.
- After that, type the formula below:
=WORKDAY(DATE(2020,1,1),1000,B5:B15)- Press Enter to see the result.
Read More: How to Use WORKDAY.INTL Function in Excel (A Complete Guideline)
9. Create Series of Dates in Excel
If we want to create arrays of dates, we can do it easily using the DATE function. The dates can have intervals if we wish. Let’s follow the procedure given below for making the series of dates.
- Firstly, write the following formula in Cell C5 and hit Enter.
=DATE(2022,5,20)
- Then, select the range of cells C5:C15 for creating the arrays there.
- Now, go to the Home tab.
- Afterward, click on the small icon beside Fill and select Series.
- Consecutively, the Series window will pop up.
- Further, select Column, Date and Day in the box.
- At the same time, in the Step value box write 7. It will create a gap of 7 days in between dates.
- Finally, press OK and we will see the series of dates created.
- Instead of the procedure mentioned above, you can use the following formula to create the series of dates.
=DATE(2022,5,SEQUENCE(11,1,20,7))- Next, hit Enter and we can see the series of dates created.
10. Convert Date to Text with TEXT and DATE Functions
Finally, if you want, you can convert a date into text using the TEXT function of Excel.
The syntax of TEXT function is:
=TEXT(value,format_text)Here, value is the value that will be converted to text and format_text is the format in which you want to get your texts. There are a lot of formats of texts that Excel accepts.
Now, to extract the text names of the month of any date, follow the steps below:
- First of all, select Cell C5 and type the formula below:
=TEXT(DATE(2021,5,20),"mmmm")- After that, hit Enter to see the result.
Common Errors with Excel DATE Function
The VLOOKUP function has the following common errors.
| Error | When They Show |
|---|---|
| #NUM! | Shows when it finds wrong arguments or large numbers. |
| #VALUE! | Shows when the argument number is of the wrong data type, like text, array, etc. |
How to Insert TODAY Function to Get Today’s Date in Excel
We can easily find today’s date in Excel using the TODAY function. Let’s follow the procedures given below.
- Just write the following formula in Cell C5 and hit Enter.
=TODAY()- And it will give today’s date as output.
Conclusion
The DATE function of Excel has versatile uses. In this article, I have shown 10 ideal examples of using the DATE function of Excel with additional examples related to dates. If you have any queries, please leave a comment.
Related Articles
- How to Use HOUR Function in Excel (7 Easy Examples)
- Use DATEVALUE Function in Excel (6 Suitable Examples)
- How to Use EDATE function in Excel (5 Simple Examples)
- Use DATEDIF Function in Excel (2 Ideal Examples)
- How to Use TIME Function in Excel (8 Suitable Examples)
- Use Excel NETWORKDAYS.INTL Function (4 Examples)
