When it comes to calculating dates in Excel, DATE is the most essential function to understand. As you probably know, Excel does not keep the year, month and day for a date, nor does it explicitly store weekday information in a cell. Instead, Microsoft Excel stores dates as serial numbers and this is the main source of confusion.
Not all Excel date functions can recognize dates entered as text values, therefore it's not recommended to supply dates directly in calculations. Instead, you should use the DATE function to get a serial number representing the date, the number that Excel understands and can operate on.
The Excel DATE function returns the serial number that represents a certain date. It has the following arguments:
Year - represents the year of the date.
Month - an integer representing the month of the year, from 1 (January) to 12 (December).
Day - an integer corresponding to the day of the month, from 1 to 31.
The DATE function is available in all versions of Excel 365 - Excel 2007.
The DATE syntax looks crystal clear and straightforward on the surface. In practice, there may be some unobvious pitfalls that the below tips will help you avoid.
Excel interprets the year argument according to the date system set up on your computer. By default, Microsoft Excel for Windows uses the 1900 system where January 1, 1900 is represented by the serial number 1. For more details, please see Excel date format.
Tip. To avoid confusion, always supply four-digit years. For example, if you input "01" or "1" in the year argument, your DATE formula will return the year of 1901.
As well as month, the day argument can be supplied as a positive and negative number, and Excel calculates its value based on the same principles as described above.
Tip. At first sight, supplying negative values in the month or day argument of the Excel DATE function may seem absurd, but in practice it may turn out quite useful, for example in the complex formula that converts a week number to a date.
Below you will find a few examples of using DATE formulas in Excel beginning with the simplest ones.
This is the most obvious use of the DATE function in Excel.
For example, to return a serial number corresponding to 20-May-2015, use this formula:
=DATE(2015, 5, 20)
Instead of specifying the values representing the year, month and day directly in a formula, you can have some or all arguments driven by of other Excel date functions. For instance, combine the YEAR and TODAY to get a serial number for the first day of the current year.
=DATE(YEAR(TODAY()), 1, 1)
And this formula outputs a serial number for the first day of the current month in the current year:
=DATE(YEAR(TODAY()), MONTH(TODAY(), 1)
Tip. To display a date rather than a serial number, apply the desired Date format to the formula cell.
The DATE function is very helpful for calculating dates where the year, month, and day values are stored in other cells.
For example, to find the serial number for the date, taking the values in cells A2, A3 and A4 as the year, month and day arguments, respectively, the formula is:
=DATE(A2, A3, A4)
Another scenario when the Excel DATE function proves useful is when the dates are stored in the format that Microsoft Excel does not recognize, for instance DDMMYYYY. In this case, you can use DATE in liaison with other functions to convert a date stored as a numeric string or number into a date:
=DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2))
As already mentioned, Microsoft Excel stores dates as serial numbers and operates on those numbers in formulas and calculations. That is why when you want to add or subtract some days to/from a given date, you need to convert that date to a serial number first by using the Excel DATE function. For example:
=DATE(2015, 5, 20) + 15The formula adds 15 days to May 20, 2015 and returns June 4, 2015.
=DATE(2015, 5, 20) - 15The result of the above formula is May 5, 2015, which is May 20, 2015 minus 15 days.
=TODAY()-DATE(2015,5,20)The formula calculates how many days are between the current date and some other date that you specify.
If you are adding or subtracting two dates that are stored in some cells, then the formula is as simple as =A1+B1 or A1-B1, respectively.
For more information, please see:
And here are a few more examples where Excel DATE is used in combination with other functions in more complex formulas:
In case you want not only to calculate but also highlight dates in your Excel worksheets, then create conditional formatting rules based on DATE formulas.
Supposing you have a list of dates in column A and you want to shade dates that occurred earlier than 1-May-2015 in orange and those that occur after 31-May-2015 in green.
The DATE formulas you want are as follows:
=$A2<DATE(2015, 5, 1) - highlights dates less than 1-May-2015
=$A2>DATE(2015, 5, 31) - highlights dates greater than 31-May-2015
For the detailed steps and more formula examples, please see How to conditionally format dates in Excel.
Though DATE is the main function to work with dates in Excel, a handful of other functions are available to tackle more specific tasks. You can find the links to in-depth tutorials at the end of this article.
Meanwhile, I'd like to present you our Date & Time Wizard - a quick and easy way to calculate dates in Excel. The beauty of this tool is that outputs the results as formulas, not values. Thus you have a kind of 'two birds, one stone' opportunity - get the result faster and learn Excel date functions along the way :)
The wizard can perform the following calculations:
For example, here's how you can add 4 different units in cells B3:E3 to the date in A4. The formula in B4 is built in real-time as you change the conditions:
If you are curious to explore other capabilities of the wizard, feel free to download an evaluation version of the Ultimate Suite below which includes this as well as 60 more time saving add-ins for Excel.
I thank you for reading and hope to see you on our blog next week!
Ultimate Suite 14-day fully-functional version (.exe file)
Table of contents