Date Formula in Excel – Table of Contents
Excel Date Formula
Date formula in excel is one of the very useful and convenient function to work with. It is needed at almost every time. In excel date is not stored originally in dd-mm-yyyy format or any other format. It is entirely in a number format. There are various methods to insert date in an excel worksheet. That is what we will learn in today’s topic. There are various shortcuts and different formulas in excel to insert date and time in Excel, such as =Today() is for the current date and =Now() is for current date and time. While speaking of excel shortcuts, CTRL + ; (Semi Colon) is the shortcut to insert date in excel cell while CTRL + SHIFT +; is the shortcut to insert current time in excel cell.
Now moving back to DATE formula, first let us look at the syntax of the DATE formula given by excel.
The default DATE formula in excel is – =DATE()
Now there are various uses of the date function. The arguments date function takes is Year, Month and Day.
Before we begin with some examples let us make some basic clear about the syntax of the DATE function.
As we saw above there are three function arguments, let us cover them up in detail.
- Year: The year component in date function represents the year value of the date. For our information, we need to know that excel uses 1900 system as a default system in dates. What I mean is if we provide a value of 23 excel will treat it as 1900+23 the thus year 1923. Similarly, if we provide a value of 119 in the year component excel gives us the value of 2019 i.e. the current year.
- Month: The month component in date function represents the month value of the date. Again we need to keep in mind that Excel uses the 1900 system for default system in dates. So if we provide a value of month above than 12 in the month component of the date function, excel adds the value we provide to it in the month component. For example, if we provide value like 15 in month component it adds it to the current year and will represent the third month in the next year as the returned value. But if the value is less than 12 then the function represents the value provided to it and there are no changes made to the date.
- Day: The third and the last component in the date formula is the day. It represents the Day value in the date function. Now here is a trick to the day component in the date formula. It can be a positive value or a negative value both. How does it work? It works on the same concept of the month component of the date function above. So if provide a positive value and it is above 31 excel adds the value to the current month and returns the value. Now the negative input may sound a bit odd but it isn’t. If we provide negative value in the day column. It subtracts the value from the current month and returns the value.
As explained above Date in excel formula is a very useful function. It is similar to the other functions of excel which gives the value for the current date. But it has three separate components of the date: Year, Month and Day.
How to Use DATE Formula in Excel? (with Examples)
Below are some of the examples of DATE in excel formula.
Date Excel Formula Example #1
For demonstration purposes, consider the below data,
We have different values of year month and day in the matrix. We will calculate the date using the DATE formula in excel in Column D for the respective data provided.
Step 1: In cell D2, Write the following Excel DATE formula,
Step 2: Select the values respective to date, month and day,
Step 3: Press Enter to see the result,
Step 4: Drag the formula to cell D6 to get the result for all the other different types of data,
Now from the results, we can deduce that when there was month greater than 12 then the value of month was added to the corresponding year so the year 1902 in the first row but the date was negative so the date was decreased by the same value.
Example #2 – Addition to DATE Formula in Excel
We can also add some values to date function and get a result. This means we add some days to the date function. Consider the following Data,
We have the above data, we will use the date function and add the value to it to see the result.
First, let us see what will be the result without the addition of the day to the formula. In cell E2 write the formula,
Press Enter to see the result.
Now in Cell F2, Write the following excel DATE formula,
Press Enter to see the result,
The value is added to the date and we get day 17 as the result.
Example #3 – Subtraction to DATE Formula in Excel
Similar to addition to the DATE formula we can also subtract the values from the DATE formula and get a result. For a demonstration, consider the following example,
We will subtract the given subtraction value from the DATE formula.
First let us calculate the date with the given data, Write the following excel DATE formula,
Press Enter and See the result.
Now we will subtract the given value from the date, in cell F2, Write the following formula,
Press Enter to see the final result.
We can see that three days were subtracted from the date obtained from the data.
Things to Remember
There are few things we need to remember about DATE function in Excel:
- Excel uses the 1900 system as the default system.
- Any value above 12 in month component will be added to the corresponding year.
- Any value above 31 in day component will be added to the corresponding month.
- Day component can be positive or negative.
This has been a guide to Excel DATE Formula. Here we discuss how to use DATE in Excel Formula along with practical examples and downloadable excel template. You may learn more about excel from the following articles –