DATE in Excel (Table of Contents)
DATE in Excel
Often in excel, we do not get the exact date that we want; I mean the formatting of the date. The preferable date format for most of us is DD-MM-YYY.
Excel works on numbers and it will display according to the format we give. Let us look at the below simple examples.
Enter the number 43434 in excel and give the format as DD-MM-YYY.
Once the above format is given now look how excel show up that number.
Now give the format as [hh]:mm:ss and the display will be as per the below image.
4.8 (279 ratings)
So excel completely works on numbers and their formats.
By using DATE Function in Excel, we can create an accurate date. For example, =DATE (2018, 11, 14) would give the result as 14-11-2018.
DATE Formula in Excel
The Formula for the DATE Function in Excel is as follows:
The Formula of DATE function includes 3 arguments i.e. Year, Month, and Day.
Year: It is the mandatory parameter. A year is always a 4-digit number. Since it is number we need not to specify the number in any double quotes.
Month: This is also a mandatory parameter. A month should be a 2-digit number that can be supplied either directly to the cell reference or direct supply to the parameter.
Day: It is also a mandatory parameter. A day should be a 2-digit number.
How to Use DATE Function in Excel?
This DATE Function in Excel is very simple easy to use. Let us now see how to use the DATE Function in Excel with the help of some examples.
From the below data create full date values. In the first column we have days, in the second column, we have a month and in the third column, we have a year. We need to combine these three columns and create a full date.
Enter the above into excel sheet and apply the below formula to get the full date value.
The Full DATE value is given below:
Find the difference between two days in terms of Total Years, Total Month, and Days.
Assume you are working in an HR department in a company and you have employee joining date and relieving date data. You need to find the total tenure in the company. For example 4 Years, 5 Months, 12 Days.
Here we need to use DATEDIF function to get the result as per our wish. DATE function alone cannot do the job for us.
If we just deduct the relieving date with joining date we get the only number of days they worked, we get in detail.
In order to get the full result i.e Total Tenure, we need to use DATEDIF function.
DATEDIF function is an undocumented formula where there is no IntelliSense list for it. This can be useful to find the difference between year, month, and day.
The formula looks a lengthy one. However, I will break it down in detail.
Part 1: =DATEDIF (B2,C2,”Y”) this is the starting date and ending date and “Y” means we need to know the difference between years.
Part 2: &” Year” This part is just added into a previous part of the formula. For example, if the first part gives, 4 then the result will 4 Years.
Part 3: &DATEDIF(B2,C2,”YM”) Now we found the difference between years. In this part of the formula, we are finding the difference between the months. “YM” can give the difference between months.
Part 4: &” Months” This is the add to the part 3. If the result of part 3 is 4 then this part will add Months to part 3 i.e. 3 Months
Part 5: “&DATEDIF(B2,C2,”MD”) now we have a difference between Year and the Month. In this part, we are finding the difference between the days. “MD” can give us that difference.
Part 6: &” Days” this is added into the part 5. If the result from the part 5 is 25 then it will add Days to it. I.e. 25 Days.
Now I will explain you the different date formats in excel. There are many date formats in excel each show up the result differently.
VBA Example with Date in Excel
Assume you are in a welfare team of the company and you need to send birthday emails to your employees if the there is any birthdays. Sending each one of them is a tedious task. So here, I have developed a code to auto, send birthday wishes.
Below is the list of employees and their birthdays.
I have already written a code to send the birthday emails to everyone if there is any birthday today.
Write the above in your VBA module and save the workbook as a macro-enabled workbook.
Once the above code is written on the VBA module save the workbook.
The only thing you need to do is add your data to the excel sheet and run the code every day you come to an office.
Things to Remember About DATE Function in Excel
- The given number should be >0 and <10000 otherwise excel will give the error as #NUM!
- We can supply only numerical values. Anything other than numerical values, we will get eh error as #VALUE!
- Excel stores date as serial numbers and show the display according to the format.
- Always enter full year value. Do not enter any shortcut year like 18, 19, and 20 etc… Instead enter the full year 2017, 2018, 2020
You can download this DATE Function Excel template here – Date Function Excel Template
This has been a guide to DATE in Excel. Here we discuss the DATE Formula in Excel and how to use DATE Function in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –