How to use DATE Function in Excel

The DATE function is the best way to create a valid date in Excel.

It can create a valid date as per Excel's date system by providing the year, month and day as a number.

In simple words, it returns a date on the basis of a year, month and day you provide.

Syntax

DATE(year,month,day)

  1. year A number to use as the year.
  2. month A number to use as the month.
  3. day A number to use as a day.

Examples

In the below example, we have used cell references to specify the year, month and day to create a date.

create a valid date with excel date function

We can also insert arguments directly into the function to create a date as you can see in the below example.

input arguments directly into excel date function

And in the below example, we have used different types of arguments to see the result returned by the function.

input different arguments in excel date function
  1. 1900 as the year for the date.
  2. If we mention -1 as a month it has gives us the date of November month of the last year. The point is, when you specify a year, month or day in negative number it will deduct that number from the starting month of the year you have mentioned.
  3. And, if you mention 0 as a month number it will give you the date of last month of the previous year.
  4. If you specify zero as a year number it will give you 1900 in the year as excel use dates from 1900 to 10000.
  5. When you mention zero as a day it will return last day of the previous month.
  6. And, if you mention the day in negative number it will deduct those days from last day of the previous month.

Remember

  1. You can enter year from 1900 to 10000. If you enter any year below than 1900 it will that number to 1900. For example, if you enter the year as 102 it will return the year 2002.
  2. It is recommended to enter a four-digit number for the year to avoid any issue.
  3. Year, Month and Day can be negative or positive.
  4. Excel store dates as serial numbers.

Sample File



  • Pawan Kumar Gupta

    👌👌👌

    • Puneet Gogia

      🙂