by 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) + 15The formula adds 15 days to May 20, 2015 and returns June 4, 2015. - Subtracting days from a date:
=DATE(2015, 5, 20) - 15The 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
Hey!
Need some help. I created some sort of Date Picker from 3 cells (A1- dates 1-31, A2- months 1-12, and A3- years) dropdowns. Using Formula in a different cell =IFERROR(DATE(A1,A2,A3);"Not a proper date")
Formula works as such, but if I pick from dropdowns 30.feb.2019, the formula returns 01.mar.2019...
How can I fix this so i get in return that the date is not correct?
how could I get remaining months? (2 years (Length of Lease) minus a date (start of Lease))?
pls, I want to do conditional formatting whereby the cell is highlighted yellow from 10 days before maturity till the day its matures. on the day of maturity, I want the cell to turn green.
HELP! I want to know a formula,
Lets say if the person join the company before year 1993, he will retire at year 55years old AND IF the person join the company in 1993 and above he will retire at year 60years old.
D12 = date of birth
G12 = date joining the company
and if its a woman, she will retire at 60years old no matter what
HELP
I want to use formula for date sorting
example: Item A have a 3 delivery date like Row 1 : 10/02/2019 Row 2: 18/03/2019 Row 3: 05/01/2019 Row 4: 20/05/2019
Than date serial number require using formula like:
Row 3: Serial number 01
Row 1 Serial number 02
Row 2 Serial number 03
Row 4 Serial number 04
Please suggest which formula use for the same.
If I use the edate formula for 06/30/2019, and then want to go back 1 month it returns a value of 5/30/2019, however I there are 31 days in May, I need the formula to figure out what month I am subtracting and caculate it correctly so that I end up with the actual end of the month day of 5/31/2019.
=EDATE(E97,B97) where E97 is 06/30/2019 and B97 is -1
I wonder if anyone can help?
I need to work out the date that a valuation total went over 90% of the total Contract value?
i.e. column A would be the valuation date and column Z is a formula with the total contract % so far, I need to work out the date when if first goes over, any way of doing this
Thanks
Kelly
I have a column titled 'Review month' and a column titled 'Requires Review', the 'Review month' column is just text (e.g May). I am trying to create a formula so that if 'Requires Review' is within a month of 'Review month' it automatically fills the cell Red and says yes. if it is not within a month then it is to fill the cell Green and say no.
Is it possible to create a formula for this or conditionally format the cell to do this?
Any further information needed then please ask.
Dear , I seek your help to change the date 12/01/2019 to 12012019 format please..
it would be great help
Hi Mohammed,
The easiest way is to apply a custom date format to the cell. Here's how:
1. Select the cell(s) you want to format.
2. Press Ctrl+1 to open the Format Cells dialog.
3. On the Number tab, select Custom from the Category list and type one of the below format codes in the Type box.
4. Click OK to save the changes.
If 12 is the month and 1 is the day, use this format: mmddyyyy
If 12 is the day and 1 is the month, use this one: ddmmyyyy
For more information, please see How to create a custom date format in Excel
Please help me with the excel formula. I am intending to mark first and second in a cell based on series of date of birth available in a different column by using a cut off date.
Dear Team,
I need formula where supposed I have dates from 1st May to 3oth May there have 100 counts.
Now I need formula where from date 1st May till 15th May= month should be May only and there days which is more then 16th till 30th May its come in next month which is june
Please help me
Not seeing it in this thread so I will ask here.
We have an event every third sunday of each month. Need a formula that can depict that and return a date.
Please refer to my problem as i want to a calculation sheet like 31/03/2014 to 15/05/2014 then the result come Mar 01 days April 30 days and May 15 days. please reply
I need to convert 13Y 11M into a date using today as the reference.
I am Calculating the Year and Month completed between two date
Start Date = 06-Jul-2009 and End Date = 31-Mar-2019
Difference of Both Date is 9 Year & 8 Months, But i want a formula to calculate easily
Please help me , i need a formula in my report to do it fast. The report is like this:
i have to calculate days taken to pay the customers, but holidays and weekends are not included. These days are calculated from the day invoice is received untill it is paid.
I would like to do a report to check if the customer paid on time:
1. This is acceptable:
(a) Due Date Due Date Due Date > 7 days
Hi,
Can you help for below query
Payment Date DUE DATE
03-04-2018 07-05-2018
02-05-2018 07-06-2018
22-06-2018 07-07-2018
25-07-2018 07-08-2018
I want to get due date by formula. Can you tell me by which formula I can get the due
date as per above.
Thanks,
Ashish Sharma
Hi
Can u pl help me how to remove slashes by using formula. Ex: 12/31/2018 (MM/DD/YYYY) to 12312018.
Thank u in advance.
I have 3 date one is TDR Raised then TDR Closed Target Date & Last TDR CLOSED DATE.
I want to calculate Lead time between TDR Closed Target date with the current date tha I have done, but after that I need to stop calculating the lead time after the TDR Closed Date.
Please suggest