What is the DATE function in Excel?
→ The MS Excel DATE function creates a valid date based on input year, month, and day.
→ DATE function is useful for assembling dates that need to change dynamically based on other values in a worksheet.
→ It is a built-in function in Excel, and we can put it in the Date/Time Function Category.
→ It can be used as a worksheet function in Excel.
→ So we can enter this function directly into the cell and we can get a serial date value.
→ A serial date is how Excel stores date internally and it represents the number of days since January 1, 1900.
What is the syntax of the DATE function?
→ The syntax for the DATE function in Microsoft Excel is:
=DATE (year, month, day)
The Syntax Parameter or Syntax Arguments of DATE function are:
➨ year - Number for a year. We can enter this value between 0 to 9999. If the year is between 0 and 1899, the year value is added to 1900 to determine the year.
➨ month - Number for a month. If the month value is greater than 12, then every 12 months will add 1 year to the year value. E.G. DATE(2016,13,4) is equal to DATE(2017,1,4) and DATE(2016,25,4) is equal to DATE(2018,1,4) etc.
➨ day - Number for the day. If the day value is greater than the number of days in the month specified, then the appropriate number of months will be added to the month value.
Note:
→ If the year is greater than 9999, the DATE function will return the #NUM! error.
→ The date value in excel is started from January 1, 1990.
→ If the year is between 1900 and 9999, the DATE function uses the year value as the year.
Examples of DATE Function:
→ For a detailed understanding, we will take some example as mentioned below
→ As we know the syntax of the Date Function =DATE (year, month, day), So we will take example accordingly.
Ex. – 01: Normal Day and Normal Month
→ As per the syntax, the formula will be =DATE(A2,B2,C2) and from the photo, we can see the result = 01-01-2020
→ This is the normal day and month case
Ex. – 02: Minus Day and Normal Month
→ Now we will take an example of minus day and normal month combination. So minus day will subtract the day from the total month’s day and subsequently the month and year value will be applicable based on the day value.
Ex. – 03: Normal Day and Minus Month
→ Similarly, minus month’s value will subtract the months from the year value.
Ex. – 04: Day >31 and Normal Month
→ If the day value is >31 then the month value will be increased based on the day vale more than the running month.
Ex. – 05: Normal Day and Month > 12
→ In this example, the month value is more than 12. So it will result in increasing the year value.
Ex. – 06: Minus Day and Month > 12
→ If the day value is negative and the month value is more than the 12 at that time the month value will result in the year increasing and the day value will result in decreasing the month value.
Ex. – 07: Day > 31 and Month > 12
→ In this case, the day value is more than the current month’s total day value and the month value is more than 12 so both will be results in increasing the month and year respectively.
Ex. – 08: Minus Day and Minus Month
→ Similarly, in this case, the minus day and month will result in decreasing the year and month value.
👉 For a regular update:
➨ Join us (Telegram Channel)
➨ Join us (LinkedIn Page)
👉 Also Navigate Our Popular Category:
➨ Lookup & Reference Functions
Post a Comment