DATE Function in Excel – Convert year, month and day into date

DATE Function in Excel converts supplied Arguments Year, month and day into an Excel date.

Syntax for DATE Function in Excel

Year, month and day are the arguments passed to the Excel DATE function. 

 DATE Function in Excel Example:

Formula:

Apart from giving cell value as argument, we can directly give the year, month and date value as argument as shown above.

Results:

 

 

How Excel Handles Date Function?

Date Function in Excel – Exceptions on Year:

• In Excel Base year is 1900
• If year is between zero and 1900, Excel will add 1900 to the year.

Example:

 

Excel Date Function  – Exceptions on Month:

Month can be greater than 12 and less than zero.

• If Month is greater than 12 then excel will add the remaining months to the next year.

Resultant date is Jan-4- 2015

Example:

• If Month is less than 0 then the absolute of those month is subtracted from the previous year.

Resultant date is Nov-4- 2013

Resultant date is Dec-4- 2013

 

Excel Date Function  – Exceptions on Day:

Days can be Positive or Negative

• If Days are positive then excel will add the remaining days to the next month.

Example:

Resultant date is April-2-2014

• If Days are Negative then the absolute of those days are subtracted from the previous month.

Example:

Resultant date is March-29- 2014

Resultant date is March-31- 2014