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

DATE(Year,month,day)

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

 DATE Function in Excel Example:

Formula:

date function in excel 1

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

Results:

 date function in excel 2

 

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:

date function in excel 3

 

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.

date function in excel 4

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.

date function in excel 5

Resultant date is Nov-4- 2013

date function in excel 6

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:

date function in excel 7

Resultant date is April-2-2014

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

Example:

date function in excel 8

Resultant date is March-29- 2014

date function in excel 9

Resultant date is March-31- 2014

next DATE FUNCTION IN EXCEL