Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

You can download the practice workbook from here.


Introduction to DATE Function

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

Excel DATE Function

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

Find First Day of Month Using DATE Function

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

Insert Last Day of Month with EMONTH and DATE Functions

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.

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.

Apply Excle DATE Function to Add Specific Days to Date

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.

Use DATE Function to Subtract Specific Days from Date


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.

Identify First Day of Year with DATE and YEAR Functions

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.

Count Total Workdays Between Two Dates in Excel

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.

Read More: How to Use the Excel DAYS Function with a Practical Example


Similar Readings


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.

Find Workday after Specific Number of Days in Excel

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)

Create Series of Dates in Excel

  • Then, select the range of cells C5:C15 for creating the arrays there.

excel date function

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

excel date function

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

Convert Date to Text with TEXT and DATE Functions

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.

How to Insert TODAY Function to Get Today's Date in Excel

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

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo