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.
Excel DATE function
The Excel DATE function returns the serial number that represents a certain date. It has the following arguments:
Where:
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.
DATE function - tips and notes
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.
Year
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.
- If the year argument is between 1900 and 9999 inclusive, Excel uses exactly the value you supplied to create a date. For example, DATE(2015, 12, 31) returns December 31, 2015.
- If the year argument is between 0 and 1899 inclusive, Excel adds the specified number to 1900. For example, DATE(100, 12, 31) returns December 31, 2000 (1900 + 100).
- If the year argument is less than 0 or greater than 9999, a DATE formula will return the #NUM! error.
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.
Month
- If the month argument is greater than 12, Excel adds that number to the first month in the specified year. For example, DATE(2015, 15, 5) returns the serial number representing March 1, 2016 (January 5, 2015 plus 15 months).
- If the month argument is less than 1 (zero or negative value), Excel subtracts the magnitude of that number of months, plus 1, from the first month in the specified year. For example, DATE(2015, -5, 1) returns the serial number representing July 1, 2014 (January 1, 2015 minus 6 months).
Day
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.
Excel DATE formula examples
Below you will find a few examples of using DATE formulas in Excel beginning with the simplest ones.
Example 1. DATE formula to return a serial number for a date
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.
Example 2. How to return a date based on values in other cells
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)
Example 3. DATE formula to convert a string or number to a date
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))
Example 4. Adding and subtracting dates in Excel
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:
- Adding days to a date:
=DATE(2015, 5, 20) + 15
The formula adds 15 days to May 20, 2015 and returns June 4, 2015. - Subtracting days from a date:
=DATE(2015, 5, 20) - 15
The result of the above formula is May 5, 2015, which is May 20, 2015 minus 15 days. - To subtract a date from today's date:
=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:
Advanced Excel DATE formulas
And here are a few more examples where Excel DATE is used in combination with other functions in more complex formulas:
Using Excel DATE formulas in conditional formatting
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:
Orange: =$A2<DATE(2015, 5, 1)
- highlights dates less than 1-May-2015
Green: =$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.
How to make date formulas in Excel with Date & Time Wizard
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:
- Add years, months, weeks, days, hours, minutes and seconds to the specified date.
- Subtract years, months, weeks, days, hours, minutes and seconds from the specified date.
- Calculate the difference between two dates.
- Get age from the birthdate.
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!
Available downloads
Ultimate Suite 14-day fully-functional version (.exe file)
680 comments
Hello! Could you help me with a formula to add (sum) two periods. For example if I wish to add 01 year, 06 months, 12 days with 02 years, 07 months, 26 days how can I do it? With what formula? The correct answer would be 04 years 02 months and 08 days. But what formula in Excel would give me that answer. Thank you.
my dog show is on 28/1/17
i want to calculate age from 4 months to 6 months, 7 months to 12 months, 13 months to 18 months, 19 months to 36 months from cut off date which is 28/1/17
Hello,
I have a start date and I have it so it tells me how many days open, but how would I get that to stop when I put in a date completed?
Thanks
I would like to calculate 18 months from each of the dates for an entire column?
hi ,
i would like to make a formula that automatic increase the value every year in April or if i change the cell to month April other cell automatic the value with the percentage increase
Hi,
How to find out the due date for one who completes the probation period i.e. 6 months. The due date should be in DD/MM/YY. Please help.
AMAZING SITE! ALL ANSWERS ANSWERED SPECIFICALLY.i would like to create a calendar with events, but i would also like the events to be automatically written out in the calendar.
is there any resources on your site that helps?
thanks.
Hello, I have used this site many times looking for solutions to my Excel problems. I am trying to figure out a way to add to dates in different columns. I am tracking things weekly beginning on Monday and ending on Sunday. Short of manually adjusting every cell, is there a formula to fix?
Example:
C1 is 1/1-1/8
D1 is 1/9-1/15
E1 is 1/16-1/22
F1 is 1/23-1/29
G1 is 1/30-2/5
etc. every week to 12/31
Hi,
how may I convert a given sample/answer (example: 3d 10h 35min) in a certain cell into a total minutes only?
Thank you.
Hi,
Need help please!
I have a date in cell B2. I also have a a table from A5 to B8 (Column A are date values and Column B are price values).
I need for Excel to use the date in B2 to search for the latest date in Column A before the date in B2, and throw me back the price from Column B.
For example:
B2=01/05/2017
A5=01/01/2017
A6=01/01/2017
A7=01/03/2017
A8=01/15/2017
B5=$3.00
B6=$3.00
B7=$3.50
B8=$3.70
I need the formula to give me the $3.50 price.
Can you please help me? I know it seems simple, but have been having a hard time with it.
Sincere regards
I'm ddoing a spreadsheet for my budget of the different bills I owe. My issue is that when I enter new dates for next year it adds 2016 instead of 2017. Can someone tell me how to fix this? I should be able to tpe in the month/date hit enter and the year auto populate but it's doing it for 2016.
TIA
Thank you for all your wonderful knowledge. Would you be able to help me do the following?
I need to have a specific starting date such as 1/1/17. I need the formula to calculate 91 days into the future that is a Wednesday closest to the 91st day but not less than the 91 days. The starting date will change every time I need to make an entry, so the formula will calculate the best Wednesday.
thank you,
Brian
I am trying to calculate the difference between starting and ending time on a work shift. The calculation is okay if the hours are all in the same day. i.e. start 8:00 am and quit at 5:00 pm.
The problem I am having is if the starting time is 6:00 pm and the ending time is 3:00 am. I was hoping the answer would be 9 hours. Instead, the answer is 15 hours.
The cells are formatted in hh:mm. The cells are not formatted like dd-mm-yyyy hh:mm because of the number of employees that need to be entered.
Hello, I am a newbie at this and trying to wing it the best i can to get a jump start. I am trying essentially to calculate 30 day, 60 day, and 90 day increments based on a random start day to arrive at an end day for late fees. I have to of course consider 30 and 31 day months and february of course being a 28 day month this year. Holidays are not an issue. Can you help me figure out how to enter this formula in excell? Thank you!
Sorry I can't find a solution to what I'm looking for in the large amounts of questions. Any help greatly appreciated:
I'd like a formula that adds a certain value, or indeed a multiplier, based on a date of the month. i.e a cell that adds another €100 automatically every 25th of the month, even better if it can stop after a year (Jan-Dec)
I'm aware there would be the very long IF function variant using TODAY and a bit of juggling, but there must be a neater formula?
Would love to hear some ideas. Thanks!
A botched workaround to my above question:
=IF(TODAY()<DATE(2016,12,31),(ROUND(((TODAY()-DATE(2016,1,1))/30),0)*[desired value]),(12*[desired value]))
Does the trick, just only comes into effect half way through the month and assumes 30 days in each month.
Would love to hear how to nail it down to a specific date.
What formula to use if you wants to know age calculation.
E.g. born on 15th May 2010 and I want to auto calculate age to the date of 31st Dec 2020 ?
Please help. Thank you
I have a spreadsheet broken down by pay periods and want to apply a formula to change the range of week dates in each subsequent cell. Example: Pay period 1 in January 2017 is 8-14 for the first week and 15-21 for the second. I want a formula to deliver 22-28 in the next cell so I don't have to look at a calendar and manually enter the dates.
Hi,
What about payroll cut-off date to be published in a cell, say for example cut-off date is from January 1 to 15, 2016. Is there a formula to show this in a single excel cell? Thanks in advance.
Hi,
The answer to the above question (# 141) is
=IF((AND(E2"",F2"")),(MIN(TODAY(),F2)-E2+1)/(F2-E2+1),"")
Hi,
I have to calculate percentage between two dates and the formula I'm using is =(MIN(TODAY(),F2)-E2+1)/(F2-E2+1) which is giving me the desired result. However, when I do not enter any dates in E2 and F2, I get a default result of 4272000% in cell G2.
I want G2 to be blank when no information is available in E2 and F2, Also it should be blank if I enter dates in one of the cells E2 or F2 or even postdates.
I would really appreciate if a formula could be devised in coordination with the above percentage formula to achieve the desired results.
Thanks in advance