HomeExcel Functions ► Excel DATE Function

Syntax For Excel DATE Function

What is the use of Excel DATE Function?

Use Excel DATE Function to create a valid date as per excel’s date system by providing the year, month & day as a number. In Simple words, It will return a date on the basis of year, month & day you have provided.

Syntax for Excel DATE Function

DATE(year,month,day)

year A number to use as a year while creating a date.

month A number to use as a month while creating a date.

day  A number to use as a day while creating a date.

More Information on Excel DATE Function

  • 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.
  • It is recommended to enter in four digit number to avoid any issue.
  • Year, Month & Day Can be negative or positive.
  • Excel store date in serial numbers.

Examples to understand Excel DATE Function

create a valid date with excel date function

In above example, I have used cell references to specify year, month & day to create a date.

Input Arguments Directly Into Excel Date Function

You can also insert arguments directly into the function to create a date as I have done in the above example.

input different arguments in excel date function

In above example, I have used different arguments.

  1. Specify 1900 as a year for the date.
  2. When I mention -1 as a month it has given me the date of November month of the last year. 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 if 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 & return the date.

Download Sample File

More Tips Related With Excel DATE Function

Conclusion

You can use Excel DATE Function where you have the year, month & day in different cells. Learn more about DATE from here.

Related Function

DateValue | DatedIf | EDate | EOMonth

Blog | Excel Functions