DATE function in Excel is a type of DATE/TIME function. It is used to make a date from given year, month, day which are provided individually. It can also be used as an assembler of dates which are changed from time to time based on values in the worksheet.
- YEAR – It is a number having value between 1 and 4 digits which represents the specified year.
- MONTH – This is in two digits and represents the specified month
- DAY- It represents the specified day value
- In General formatting of Excel, DATE function gives you result in form of a serial number, to get it in a readable form you have to apply the number format function.
- If you enter year value in range of 0 to 1899, then it automatically adds the year value to 1900 for determining the year.
- If you enter year value in range of 1900 to 9999, then same year acts as the year value.
- Month value can also be more than 12 and also can be lower than 0. If it is more than 12 than extra month is added with the first month. If it is 0 or lower than 0 than absolute value of month plus one is subtracted from the 1st month of the year provided. (ABS(MONTH)+1). Same procedure is also applicable to the day value if it is negative or positive.
- The DATE function is also used with COUNTIFS and SUMIFS functions which add a dynamic nature to these functions and they update automatically when date value is changed.
Here we have added year, month, day in respective columns and used the DATE function to enter date in format mm/dd/yyyy.