The best way to create a valid excel date.

Whenever you have to create a valid excel date you can use DATE function for that. Excel DATE Function 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 will return a date on the basis of a year, month and day you have provided.

Syntax

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.

• 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 and Day can be negative or positive.
• Excel store date in serial numbers.

Examples

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

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

In the below example, I have used different arguments.

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

What’s Next?

To learn more about Excel DATE Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There are also you have DATEVALUE  function which is highly useful like date function. Apart from this, I have a list of excel functions and some real life formulas examples.

• Pawan Kumar Gupta

👌👌👌

• Puneet Gogia

🙂