How to Use DATE Function in Excel (10 Ideal Examples)

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.

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

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 numbers, 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.

How to Use DATE Function in Excel: 10 Ideal Examples

There are various uses of the DATE function in Excel. Here, I will discuss the procedure to use the 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

In the formula, C5 contains the date, and the DAY function excludes the day value from the date. 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.

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)

Here, 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.

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.

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.

In the formula, 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.

In the argument of the NETWORKDAYS function, C5 and D5 contain the two dates, B5:B15 is the range of cells that contain the holiday’s date.

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

Here, 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.

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.

Here within 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.

Download Practice Workbook

You can download the practice workbook from here.

Use of DATE Function.xlsx

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.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!