How to Use DATE Function in Excel (8 Examples)

Today I will be showing how you can use Excel’s DATE function with Formulas.

Download Practice Workbook

Excel DATE Function with Formulas (Both Array and Non-Array).xlsx

Excel DATE Function

Syntax

The syntax of the DATE function is:

=DATE(year,month,day)

Output

And it returns the date with the given year, month, and day.

DATE(2021,5,20) = 20-May-2020

[You can always change the format if you wish].

Now let’s have a look at the data set.

We have the names of some candidates and their interview dates for a company called Marlo Group.

See, we have the interview dates inserted for each of the candidates using the DATE function of Excel.

The date for the first candidate Richard Hockley is:

=DATE(2021,5,20)

Here 2021 is the year.
5 is the number of the month.
And 20 is the number of the day.

DATE Function with Numbers

In place of the three arguments of the DATE function (year, month, and day), we can place plane numbers as shown earlier.

For example, =DATE(2021,5,24)

Now, we know in English Calendar, some months consist of 31 days, some of 30 days, and some of even less (28 or 29 days).

If you input the day argument greater than the number of days of that month, the DATE function will simply add one extra month and then keep the rest number of days.

For example,

DATE(2021,6,32) = 2-Jul-2021

As the 6th month (June) consists of 30 days, it has moved to the next month July and kept the rest 2 days.

A similar case happens for months. We know, there are a total of 12 months in a year.

So, if you enter a month argument greater than 12, the DATE function will add one more year and keep the rest number of months.

DATE(2021,13,5) = 5-Jan-2022

As the month number is 13, it has moved one year forward to 2022 and kept the rest 1 month (January).

Now the cases of zero or negative numbers. If you enter any zero or a negative number as the day argument, the DATE function will move to one month behind and then count the days backward.

DATE(2021,8,-6) = 25-July-2021

See, DATE(2021,8,-6) moves one month behind from the 8th month August to the 7th month (July) and then goes to the day number 25 counting 5 days backward (31… 30… 29 up to 25) (Starting from 0).

And the similar thing for months also. If you enter one zero or a negative number as the month, it will move to one year behind and then count the months backward.

DATE(2022,-5,24) = 24-July-2021

As the month number is negative, (-5). It has moved from 2022 to 2021 and counted 5 months backward to June (Starting from 0)

Now if you understand this, can you tell me which date does the formula DATE(2022,-6,-7) indicates?

Yes. You are right.

It indicates 24-May-2021.

First, it moves one year back to 2021 and then counts 6 months from behind to June, Then it again goes one month back and counts 7 days backward to 24.

So the date becomes:

=24-May-2021

DATE Function with Cell References

Up till now, we have used pure numbers as the year, month, and the day argument.

You can also use any other cell reference in place of these arguments.

For example, we created a new column C with numbers as days.

Then used this formula for the first candidate, Richard Hockley.

=DATE(2021,5,C4)

As C4 contains 13, it becomes DATE(2021,5,13) = 13-May-2021.

We entered the date for the rest of the candidates by dragging the Fill Handle.

Of course, you can use cell references for the year and the month arguments too.

DATE Function with Array

Up to this point, we have entered three single values as the three arguments of the DATE function.

But if you want, you can enter an array of values as an argument of the DATE function, and you will get an array of outputs.

For example, in the previous section, if you want all the interview dates together, use this formula:

=DATE(2021,5,C4:C20)

It is a combination of total 17 formulas:

DATE(2021,5,C4) = 13-May-2021
DATE(2021,5,C5) = 23-May-2021

DATE(2021,5,C6) = 13-Jun-2021
...

...

...

DATE(2021,5,C20) = 20-May-2021

As it is an Array Formula, do not forget to press Ctrl + Shift + Enter and then drag the Fill Handle (Unless you are in Office 365).

Summary

So, the DATE function takes three arguments (year, month, and day) and returns the date.

The arguments may be any pure number, any cell reference containing a number, or an array of numbers.

Formulas Utilizing Excel’s DATE Function

Inserting Today’s Date

To insert today’s date in any cell, you can use the TODAY() function of Excel. It takes no arguments, just returns today’s date.

The syntax is:

=TODAY()

See, we have today’s date, 1-Aug-2021.

Note: TODAY() function is not updated automatically. Each time you want to update it, you have to press F9 or re-enter the formula.

TODAY() function collects today’s date from your computer. So if your computer has the wrong date set, it will show the wrong date.

Inserting the First Day of Any Month

Inserting the first date of any month is simple. Just use 1 in place of the day argument.

For example, to get the first day of month September of 2021, the formula will be:

=DATE(2021,9,1)

But extracting the first date of the month from that date is not that much easy.

For example, let’s have a date, 15-September-2021 in cell C4.

How can we extract the first date of that month from this date?

To extract the first date of the month, we will use the formula:

=C4-DAY(C4)+1

Or you can use the date inside the formula in place of cell reference:

=DATE(2021,9,15)-DAY(DATE(2021,9,15))+1

Now let’s see the formula in detail.

DAY function returns the day number of any date entered into it. Here DAY(DATE(2021,9,15)) returns 15.

Excel accepts any date as a numerical value. So we can add or subtract dates in Excel, just like we can do it with any numbers.

DATE(2021,9,15)-DAY(DATE(2021,9,15)) subtracts 15 days from the date 1-September-21 and returns the last day of the previous month, 31-August-2021.

Then we added 1 with it to get the first date of this month.

Inserting the Last Day of Any Month

To insert the last date of any month, you have to enter the last day of that month in the day argument of the DATE function.

For example, to get the last date February of 2021, the formula will be:

=DATE(2021,2,28)

But how to extract the last date of the month from any given date?

For example, if you have 12-February-2021 in a cell, how to get the last day of the month of that date?

You can use the EOMONTH function of Excel to extract the last date of the month from any given date.

The Syntax of the EOMONTH function is:

=EOMONTH(start_date,months)

Start_date is the date the last date of the month of which date we want to extract. Here it is 12-February-2021.

Months is the number that denotes the number of months we want to move forward by. If we want to get the last date of the original month, February then it is 0. If we want the last date of the next month, then it will be 1, and so on.

So, to get the last date of the month with the date 12-February-2021, the formula will be:

=EOMONTH(DATE(2021,2,12),0)

And to get the last date of the month 3 months later, the formula will be:

=EOMONTH(DATE(2021,2,12),3)

See, we have the last from 3 months later, 31-May-2021.

Calculating a Date Based on Another Date

Now as we know, dates can be added or subtracted just like the numbers, we can calculate any date based on a given date.

=Date + Number of Days

For example, to find out the date after 265 days from the date 2-May-2021, the formula will be:

=DATE(2021,5,2)+265

See, the date after 265 days is 4-November-2021.

You can use subtraction in the same way.

Counting Total Number of Days Between Two Dates

Counting the total number of dates between two dates is also easy.

Just subtract the two dates if you want to include the last date, but not the first date.

=Last Date - First Date

For example, the total number of days from 22-Feb-2016 to 1-Jan-2020 including 1-Jan-2020 but not 22-Feb-2016 will be:

=DATE(2020,1,1)-DATE(2016,2,22)

See there are a total of 1409 days.

If you want to include both the first and the last date, add 1 in the formula:

=DATE(2020,1,1)-DATE(2016,2,22)+1

And if you want to exclude both the first and the last dates, subtract add from the formula:

=DATE(2020,1,1)-DATE(2016,2,22)-1

Counting Total Number of Workdays between two Dates

You can count the total number of workdays between two days using the NETWORKDAYS function of Excel.

Syntax:

=NETWORKDAYS(start_date,end_date,[holidays])

Start_date is the starting date.

End_date is the ending date.
And [holidays] is a list of holidays.

So the total workdays between 1-January-2020 to 31-December-2021 with B4:B20 as a list of holidays will be:

=NETWORKDAYS(DATE(2020,1,1),DATE(2021,12,31),B4:B20)

We see, there are a total of 512 workdays.

Note: If you want to exclude the weekends too, use NETWORKDAYS.INTL function.

Finding the Workday after a Specific Number of Days

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:

=WORKDAY(start_date,days,[holidays])

Start_date is the starting date.

Days is the total number of workdays between the two dates.
[holidays] is a list of holidays.

To find the workday after 1000 days starting from 1-January-2020, with B4:B20 as a list of holidays, the formula will be:

=WORKDAY(DATE(2020,1,1),1000,B4:B20)

Similar Readings

Creating a Series of Dates

If you want, you can create a series of dates with a specific interval of days in between.

Let’s go back to our original problem. Think Marlo group wants to take interviews after every 7 days.

Now we will create a series of interview dates.

First, enter the first date.

Then select all the cells and go to Home>Fill Option in Excel Toolbar under the section Editing.

Click on Fill. From the drop-down menus, select Series.

You will get a dialogue box called Series.

From Series in, Select Column. From Type, select Date, and from Date Unit, select Day.

In place of Step value, enter your interval between two dates. In this case, 7.

Click OK. And you will get a series of dates with interval 7.

If you do not want to use it this way, you can use a formula.

The formula to create series of dates of 7 days interval starting from 20-May-2021 will be:

=DATE(2021,5,SEQUENCE(17,1,20,7))

Here within the SEQUENCE function, 17 is the total number of days of the series (C4:C20), 20 is the starting day (20 May) and 7 is the interval. You use it according to your needs.
Within the DATE function, 2021 and 5 are the starting years and months respectively (May 2021)

Note: The SEQUENCE function is only available in Office 365.

Converting Date to Text

Finally, if you want, you can convert a date into text using the TEXT function of Excel.

Syntax

=TEXT(value,format_text)

Value is the value that will be converted to text.
Format_text is the format in which you want to get your texts. There are a lot of formats of texts that Excel accepts.

To extract the text names of the month of any date, use this formula:

=TEXT(DATE(2021,5,20),"mmmm")

Note: Here “mmmm” is the format for months. Use the one that you need.

Conclusion

Using these methods, you can perform various tasks related to the DATE function of Excel. Do you have any other questions? Feel free to inform us.