Excel Date Formula (Table of Contents)
DATE Formula in Excel
There are multiple numbers of data types in excel, and Date is one of them. Date data type range from 1/1/100 to 31/12/9999. Its length is 8 bytes. Therefore, while working or calculating dates in excel, one should know the Date function.
We can define the date function as the function that returns the sequential serial number representing a date.
Formula or Syntax
Year – Year represents the year that belongs to the required date.
Month – Month represents the month that belongs to the previously stated year.
Day – Day represents the date of the month.
How to Use Date Formula in Excel?
Date Formula in Excel is very simple and easy to use. Let us now see how to use the Date Formula in Excel with the help of some examples. These examples will surely help you with a clear understanding of the function.
Before discussing the Date() function examples, it is better to understand how Excel interprets the arguments given in the Date() function.
As mentioned earlier, the date range starts from 1/1/1900 to 31/12/9999. Hence, we will see how excel will interpret if we give year, month and date out of its range.
Year – If we give the year argument as 300 using the Date formula, it will add 300 to 1900 and give results as the 2200 years. Observe the below screenshot for reference.
Month – We already know that there are 12 months. If we give a number more than 12, it will repeat to give from January again, which means the second month will be February if we give 14, 12+2.
Date – Similarly, there are also 31 days for a few months and 30 days for a few months depending on the month; it will calculate the number given in the date argument and return the results. Consider an example of February, which has 28 days. So, if I give 30 in the date argument, it should result in 2 because 28+2 will result in 30.
We can use negative values also in these arguments except for the year. Using a negative value in the year will return results as #NUM! as below.
If we give a negative value for the month argument, the function will reduce that number of months from that year and will return results. In the below example, we took the year as 2019, but we gave -3 in the month argument; hence it reduced 3 months from 2019 return results as the 9th month of 2018 with the same date.
The same logic will apply to the day also.
Examples of Date Formula in Excel
Following are the different examples:
Example #1
A simple date format can be used as below.
If we want the serial number for that date to convert the cell to a General format.
The above number represents the number of days from 1.1.1900 to the given date.
We can refer to the current year and current month using the today function instead of giving the number in the argument.
Similarly, we will do it for a month now. Instead of using the month number, we will use the Function Today() to extract the month from that.
Observe the above screenshot where I did not give the month number but used the function today for month extraction.
Example #2
Suppose we have details of the required date in different cells, which means a year in one cell, a month in another cell, and another cell. Then, we can refer to the cell address in the formula.
In the above screenshot, the arguments for the year, month and date are given with the cell address of column B.
Example #3
We can add the required number of days or subtract the required number of days to the existing date function to obtain calculated results.
Consider a situation that we will receive the cash after 45 days of the invoice date; then, we can use this formula to find a cash receipt date.
In the above screenshot, we added 45 to the Date() function, which results in the change in the month and date. For example, on the date, the function month is the 9th month, but after the addition of 45 days, the month became the 10th month. To observe that clearly, I used the month function also.
Now we will subtract 10 days from the Date() function and will check.
Observe the screenshot above we have subtracted 15 from the Date function because of which month has changed from 9th to 8th month. So the date has changed from 10 to 26. This is how we can perform addition and subtraction.
Example #4
Sometimes people may give the date in the format like 10022019, which they mean 10/02/2019.
With the help of the Date() function, we can also change the format from 10022019 to 10/02/2019 or 02/10/2019.
Observe the formula in the above screenshot. I used 3 functions right(), mid () and left(). Let me explain how it works.
We used RIGHT(A1,4) in place of the year, which means, in cell A1, consider the data from the right end to 4 positions nothing but 2019. We used MID(A1,3,2) in the month’s place, which means, in cell A1 from which position and how many values we need. For example, we need from the 3rd position and two values hence given 3 and 2. In place of date, we used LEFT(A1,2), which means from the Left end two values which are 10. By combining all those values, it will convert the date of string format to date format.
Finding the Difference Between Two Days
If we want to find the difference between two days, we should use the DATEDIF() function. By using this DATEDIF, we can find the difference in days, months and years separately.
For this, we just need to give two dates in which we want to find the difference and the format in which we are looking for difference, whether days, months and years.
The above screenshot marked with a red color box reflects the required format of results if you observe.
M – for Months
D – for Days
Y- for Years
Things to Remember About Date Formula in Excel
The date function will take the input from the user for a date, month and year; they will give results in the serial number or date format.
The serial number is the calculation of the number of days from 01.01.1900 to a given date. If you want to see the serial number in date format, change to date format.
Recommended Articles
This has been a guide to the Date Formula in Excel. Here we discuss How to use Date Formula in Excel along with practical examples and a downloadable excel template. You can also go through our other suggested articles –
