How to Use DATE Function in Excel (8 Examples)

Dataset with DATE Function in Excel

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


Download Practice Workbook


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.

Dataset with DATE Function in Excel

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)

DATE Function with Numbers

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

DATE Function with Day More than 30

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

DATE with Months More than 12

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

DATE with Negative Day

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

DATE with Negative Month

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.

DATE with Both Month and Day Negative

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)

DATE with Cell Reference

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)

DATE Function with Array

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

TODAY Function of Excel

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)

First Date of a Month

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

First Date of a Month from Another Date

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.

DAY Function of Excel

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.

Number Subtracted from a Date

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)

EOMONTH Function of Excel

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

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

EOMONTH Function of Excel

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

Number Added with Date

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

You can use subtraction in the same way.

Read more: How to Use the Excel DAYS Function


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)

Subtracting Two Dates

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)

NETWORKDAYS Function in Excel

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)

WORKDAY Function in Excel

Read more: How to Use WORKDAY.INTL Function in Excel


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.

One Date in Excel

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

Fill Option in Excel

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

Fill Drop Down Menus

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.

Series Dialogue Box in Excel

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

A Series of Dates

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

Series of Dates Using Formula

  • 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")

TEXT Formula in Excel

Note: Heremmmm” 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.


Further Readings:

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