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
Hi:
Let's say that I have columns A and B. I need to insert a date in column A and automatically appears a date but 90 days later in column B. How can I do this?
Thank you.
Hello Camilo
use this Formula your worksheet.
=TEXT(SUM(A1+60),"DD-MMM-YY HH:MM")
A1- Columns A
Formula use - Columns B
i think that we works.
Hi,
I am working on next year's budget: Let's say an employees hire date is June 1, 2017 and they will be getting anywhere from a 0% - 3% merit increase on June 1, 2018.
1 - I need to calculate the current salary amount from January 1, 2018 to June 1, 2018 at the current hourly rate ($10) by the number of hours worked per week (20) from January 1 - June 1.
2 - I need to calculate the merit increase (3%) from June 1, 2018 to December 31, 2018 to get the new salary amount for that period.
3 - I need to know what my total budget would be for the year and is it possible to get the total dollar amount in one cell?
4 - Is it possible to create a formula to auto update this each year without having to manually change the year?
Hi guys,
Here's what i'm trying to get.
I have a list of students with date of births, the rule to enter sports tournaments are the following
U11 (Year 6/ Grade 5)– 1st Sept 2006
U10 (Year 5/ Grade 4)– 1st Sept 2007
U9 (Year 4/ Grade 3)– 1st Sept 2008
U8 (Year 3/ Grade 2)– 1st Sept 2009
U7 (Year 2/ Grade 1)– 1st Sept 2010
U6 (Year 1/ KG)– 1st Sept 2011
The date of births are set up this way 21-Dec-2008 (8.9) and i would like to have a formula which will indicate if a students is allegeable to play for the U11 or U10 ....
thank you in advance
I have hundreds of items with different project periods. Some have reports that have to be done monthly, quarterly, semiannually and annually. We roll the log of these items each month to track those that are currently due in a particular month. Does a formula exist that can calculate these due dates automatically??
Today's Date 9/20/2017
Month Ending 9/30/2017
Project Period Report Due
Project A 10/1/2016-9/30/2020 Q ?
Project B 4/1/2017-3/31/2019 A ?
Project C 9/30/2016-9/29/2018 SA ?
Project D 7/1/2015-6/30/2018 M ?
I need to use formula to calculate expiry date from age using following condition. If age is less than 40yrs, expiry date should be 5 years from examination date. If age is equal to or more than 40yrs, expiry date should be 2 years from examination date. Please help. "D" TO BE DONE USING FORMULA.
A B C D
1 Age Examination Date Expiry Date
2 57 7/25/2017 7/24/2019
3 39 7/25/2017 7/24/2022
4 40 7/25/2017 7/24/2019
Hello, Sara,
in addition to the DATE function (to add years) you need to use IF function as well. You will need to built a nested IF in order to go through all your conditions. So the formula should start like this:
=IF(B2<40,DATE(YEAR(C2)+5,MONTH(C2),DAY(C2)),IF...
And your nested IF continues :)
Please follow the links from my reply to learn how the functions work and to insert the rest of your conditions properly.
I need some help data 1 is same name but different output date.but data 2 is Unique name,so i need for which date in higher date only we have represent the output date.
Data 1
hai 4/20/2016
hai 4/22/2016
hai 4/21/2016
Data 2 output
hai 4/22/2016
I need some help data 1 is same name but different output date.but data 2 is Unique name,so i need for which date in higher date only we have represent the output date.
Data 1
hai 4/20/2016
hai 4/22/2016
hai 4/21/2016
Data 2 output
hai 4/22/2016
Change date Format
20 April 2019
22 April 2019
21 April 2019
22 April 2019
And use this formula =LARGE(B5:B8,1)
B5:B8 - Select Cell range area
Journey start (12/5/2017. 6:00 AM )
return time (16/5/2017. 9:00 AM )
Return have total dinner, lunch, breakfast, and accommodation quantity.
Condition:
after 7:00PM he will not get dinner.
after 1:00AM he will not get accommodation.
after 8:00AM he will not get breakfast.
after 12:00pm he will not get lunch
Hi Team,
Your above methods are very helpful.
I wanted to know how i can get the date of next month.
For eg-: if i input 23-01-2017 i should get the output 22-02-2017
Hi,
Thank you for your question.
Enter your date in any cell (A1 in my example) and try the following formula:
=A1+DATE(0,1,0)
As the logic of the DATE function is the following: DATE(year, month, day), the formula above adds a month to the date in A1. You can change the number of days/mounts/years as needed.
Please let us know if you have any other questions.
ty so much for your amazing page and information.
I am trying to create a spreadsheet for my sales tracking. If I ordered a specific document on xx/xx/xx date and have input that date into Column A2 for example(labled -date requested), I will input the received doc. date in column B2.
But if no document is received. I would like for column B2 to turn green(when column A2 date is input(day1) and yellow when 1 day past the initial A2 date, and red when 2 days or more past the initial A2 date(request date) to inform us that the document has not been received. However, if a date has been inputted into B2 these rules(Red, yellow and green) will stop, because the date was added to B2 therefore implying that the document was received.
I have column D for the date an item is issued. column E will calculate the date from column D plus 180 days. I would like to be able to input the current date into column F. And have it calculate into column G the amount of days between the current date in column G and the due date in column E. If you can help me with formulas I would be greatful
hello, what the formula of show month period like 01 January 2017 to 31 January 2017
=text(a1,"DD MMMM YYYY")
A1- Range(which cell selected) apply formula
Hi..
I need an urgent help. I am maintaining a sheet where Supplier ID and Term start date is being recorded. Each supplier is appearing multiple times with a different term start date. I want to automatically put the respective term expiry date which will be a day before next term start date. Please help me with this.
Thanks
Can I update the cell references in a formula (i.e. add the next months value into the formula) by changing the month in a drop down list? I am looking to extend the formula each month and would like to accomplish this without manually changing every line.
Hello, Sean,
yes, Excel formulas recognize the values chosen from the drop-down list and return the values accordingly. Make sure that you reference the cell with a drop-down list correctly in your formula and it should work.
Suppose I want to have a column that has dates of completion of training and I want to have function where there are three colors, green is considered current, yellow is 60 days coming up on expiration and red has expired. How can I create this? Is it even possible? Thank you.
Hey!!i am working on an hotel management excel where i am unable to calculate or either put date meaning i am not able to adjust check in time and check out time..Pls Help
Dear Sir
very simple formulas
Hello, i have a different value on Column A, and i have different date on column B. And I want to know the beginning & ending date for that particular value. What is formula for that? Thanks
Hi there
Wondering if you could help me with this!?
Having a xcel file and im trying to get xcel to give me the proper dates depending on a bar code and the no. of days which the product has! So lets say i have:
C4(bar code), H4(life shelf 12 days),I4(day which should be)
Hi, Adrian,
if C4 contains the serial number that Excel sees as a date, there should be no problem at all. Please try the following formula in I4:
=C4+H4
Hi there,
I am using a Gantt Chart I made years ago and can't figure out why I created the formula the way I did.
=(J5-WEEKDAY(J5,1)+M8)+7*M1
J5 = is the Start Date (which is 1st of June, 2017)
M8 = 1 (Day of the week which was Sunday)
and then I have M1 whose value is 0 <-- this is the part that I needed to remember/understand, why I declared the value as 0
Given that the Start Date is 1st of June, the actual value I get is the 28th of May
Hi Daer All,
Pls someone help in my problem
I have a problem for calculating salary between two dates e,g Logic 1 Manual calculation & logic 2 is the based on the two dates. When calculated from logic 2 there is different between logic 1 amount 1,797. The case listed.
Logic 1
Manual Days calculation
Start date End Date Salary Montly days Formula Prorata salary
24-Mar-17 31-Mar-17 49,500 8 =E9/31*F9 12774
Manual 12 Months calculation
Start date End Date Salary Montly Months Formula Prorata salary
1-Apr-17 31-Mar-18 49,500 12 =E14*F14 594000
Total days+Montsh 606774
Logic 2
Start date End Date Months Salary Montly Formula Prorata salary
24-Mar-17 31-Mar-18 12.22 49,500 =F2*E2 604,977
Total 604,977
Different Logic 1 Vs Logic 2 1,797
Months calculation formula
=(DATE(YEAR(C17),MONTH(C17),DAY(C17))-DATE(YEAR(B17),MONTH(B17),DAY(B17)))/(365.25/12)
Note : Logic 1 is giving correct amount.