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.

**Table of Contents**hide

## 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.

**Note:**If we insert the number of days greater than the total days of that month then the function will subtract the day’s value and add it to the number of months.

### 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`

**C5**contains the date,

**the DAY function**excludes the day value from the date. And by subtraction of the day value from the date and adding

**1**gives the first date of the month.

- 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)`

**the EOMONTH function**takes the output date from the

**DATE**function and finds the last date as we input

**0**in the argument. If we input

**1**or any other number that number will be added to the last date of the month.

**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.

**Note:**Here, we just used (

**+**) operator for addition.

**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)-265`

In 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.

**the YEAR function**finds the year value from the date of

**Cell**

**C5**which then acts as an argument of the

**DATE**function, the month and day arguments are set to

**1**for finding the 1st date of the year.

### 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.

**Note:**If we subtract a date from a previous date the output value will be negative.

### 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.

**NETWORKDAYS**function,

**C5**and

**D5**contain the two dates,

**B5:B15**is the range of cells that contain the holiday’s date.

**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])`

**start_date**is the starting date,

**days**is the total number of workdays between the two dates and

**[holidays]**is a list of 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.

**the SEQUENCE**

**function**,

**11**is the total number of days of the series (

**C5:C215**),

**20**is the starting day (

**20 May**) and

**7**is the interval. You use it according to your needs. Within the

**DATE**function,

**2022**and

**5**are the starting

**years**and

**months**respectively (

**May**

**2021**).

### 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.

**Note:**Here “

**mmmm**” is the format for

**months**. Use the one that you need.

## 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.

**Note:**The

**TODAY**function needs no argument.

**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)**