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
hi ld like to ask what formula should I use for this. in E2 I have 01/01/2016. I want to create a formula so it will be converted to January in F2. what should I use?
Hi Kit,
You can do this in 2 ways:
1. In F2, enter the formula =E2, and then apply the custom date format: mmmm
You can find the detailed explanation about custom formatting here: How to create a custom date format in Excel.
2. Use this formula: =TEXT(E2, "mmmm")
The difference between the above approaches is as follows:
1. Changes only the visual representation but the underlying value in F2 is the full date and it can be used in other calculations.
2. The value in F2 is a text string and not a date.
OMG! You are amazingly super smart. The 2nd option worked for my need and report :). I really appreciate your help. you are AWESOME!
HI there,
I am currently creating a schedule where I want to go back 1 week -10 daysfrom a numerical date in a cell, but within that one week-10 days I want excel to choose the wednesday furthest back, how would I go about doing that.
Say my due date is September 1, 2016, going back 10 days will take me back to August 22 (monday). I want the cell to compute Wednesday, August 24. I have different dates to set up so it'll never be consistent like 5 days before etc. and I don't want to have to change for each instance.
Any help would be greatly appreciated!
hi,
i need some help/tip in calculating difference between two dates in one cell. i also try datedif formula but in office excel 2007 is don't exists. i am also look for help for converting 'ddmmyyyy'(7121985/31052005) in 'dd-mmm-yy'(07-Dec-85/31-Mar-05). I required all this for excel sheet 2007. Please help me.../////
Hii
equation for showing a particular date for a dategroup(means a week or a countious 5 days, workdays etc)
eg:01-05-2016 to 05-05-2016 is shown in a cell as 01-05-2016.
Hi,
I hope you can help me with my problem, i have a Received date and Fixed date in Cell A1 and B1, i need to calculate if A1 starts at <or=8:00 AM to 4:00 PM,maybe we can put it in C1 as Yes and for No. Then another calculation is closing Date if all Yes in C1 is closed at 5:00 PM, in No Calculate if closed within 24 hours. This except Sat and Sun.
Regards,
Dennis
I would like the return value of the cell to be the start of the current year if an employee started prior to the year starting and if they started within the year to return their hire date. Can you assist?
hello
How to calculate Total Exp( Sample Format : 10 Years 6 Months & 12 Days)
When i'm using DATEDIF() function it gives only year
please give me the idea how i can get Sample Format : 10 Years 6 Months & 12 Days
Hello Anusha,
You can use the following formula:
=DATEDIF(A2, B2, "y") &" years " &DATEDIF(A2, B2, "ym") &" months & " &DATEDIF(A2, B2, "md") &" days"
Where A2 is the start date and B2 is the end date.
I'd like to know if this is possible.
I'm trying to get how many days are my computers hired in certain months.
Say, 1 computer is hired from June 10 to July 20, 2016.
Is their a turnaround/ formula on how to get the days hired in june and july in separate columns.
So for june I get 20 days hired, and july 20 days hired.
thanks!
Dear Giovanni,
(1)How is your data organised? Are the start dates and end dates in 2 columns? Is the name or ID of the computer mentioned in another column? Can you send the details of the data in brief?
(2) In the period 10 June to 20 July, June has 21 days. Is the first day to be ignored?
When posting a question, please be very clear and concise.
Vijaykumar Shetye, Goa, India
Hi,
Thank you for giving such wonderful tips, i want to calculate daily productivity on excel like column A date column B references of accounts attended, how i can calculate number of accounts attended on a particular date.
Regards,
Dear Muzzamal Azeem,
Use this array formula is column E
=SUMPRODUCT(IF($A$1:$A$100=D1,1,0),IF(LEN($B$1:$B$100)>=1,1,0))
Array formulas are entered with Control+Shift+Enter instead of Enter.
List of dates is in column D, and your data is in column A to B, as you have mentioned in your post.
A B C D E
1 01/01/2016 A/C 01-Jan 2
2 01/01/2016 A/C 02-Jan 4
3 01/01/2016 03-Jan 4
4 02/01/2016 04-Jan 5
5 02/01/2016 A/C 05-Jan 3
Vijaykumar Shetye, Goa, India
please help me how to calculate the days from 15-5-2016 to 26-5-2016 excluding holidays falling in the first 5 days from 15-5-2016 to 19-5-2016
Dear Ravish,
Use the below formula
=NETWORKDAYS(A1,A2,B1:B100)
Cell references are as follows
Start date in cell A1,
End date in cell A2,
List of holidays in cells B1 to B100.
Change the cell references as required.
Vijaykumar Shetye, Goa, India
Hi there,
I need some help with calendars. I manage rent payments and one of the tenants does not speak English well.
I have the dates she paid her rent in column A and it starts from 2014 until now.
I would like to show her a calendar indicating, when she did not pay on time.
Therefore I would need a calendar like
S M T W T F S
1 2 3 4 5 6
7 8 9 ......
and a formula which tells the calendar which cell to highlight for rent payments, means the dates from column A.
Is there an option for this?
Dear Sir,
How to calculate date value between two date.
Vendor Name Item Qty Date
AB POLE 20 10/5/2016
AC POLE 50 15/05/2016
ABC POLE 20 18/05/2016
AB POLE 10 20/05/2016
AC POLE 25 22/05/2016
ABC POLE 30 24/05/2016
ABC POLE 10 26/05/2016
AB POLE 20 28/05/2016
AB 50
AC 75
ABC 60
Above the date sheet, i want the result if the qty purchase 50 pcs by ABC than shown date interval i.e.18-05-2016 to 26-05-2016
Dear Praharsh Tiwari,
Use the following Array Formula
=B12&" "&SUMIF($B$3:$B$10,B12,D3:D10)&" "&TEXT(MIN(IF($B$3:$B$10=B12,$E$3:$E$10,999999)),"dd-mm-yyyy")&" to "&TEXT(MAX(IF($B$3:$B$10=B12,$E$3:$E$10,0)),"dd-mm-yyyy")
I have entered it in C12. Change the cell references as required.
To Enter an Array Formula, you have to click Control+Shift+Enter instead of Enter.
A B C D
Vendor Name Item Qty Date
1 AB POLE 20 10/05/2016
2 AC POLE 50 15/05/2016
3 ABC POLE 20 18/05/2016
4 AB POLE 10 20/05/2016
5 AC POLE 25 22/05/2016
6 ABC POLE 30 24/05/2016
7 ABC POLE 10 26/05/2016
8 AB POLE 20 28/05/2016
10 AB AB 50 10-05-2016 to 28-05-2016
11 AC AC 50 15-05-2016 to 22-05-2016
12 ABC ABC 45 18-05-2016 to 26-05-2016
Vijaykumar Shetye, Goa, India
I ma trying to develop a schedule that will be comprised of six 10 day parts depending on the start date and holidays. I want to be able to change the start date and have the six parts provide the start and end dates for each part. To clarify, if there is a holiday in one of the parts I want to have that day subtracted from the total (instead of added) so that there would be fewer working days (than 10) and the dates would reflect this difference for any parts that have holidays otherwise the part should be 10 days. Example: Start June 29, 2016 10 days =WORKDAY(June 29 2016,10, July 4, 2016) returns July 12 I need for it to return July 8, 2016 which is 10 working days from June 29 minus 1 day for the holiday.
Hello,
I am currently updating my company's Excel files. I wanted to add the day's date in a cell if a project is marked completed. ive' used the following formula: =IF(ISTEXT(U:U), NOW(), ("")). it worked but I am facing a problem. the projects that were marked completed had the date in the cells next to them but the dates keep changing to today's date or the current day i open the excel file. can you please help me with telling me the right formula to add an unchanging date using IF function?
Thank you,
Maan
I need a column to determine who is a minor that (using YEARFRAC function) would automatically note who is minor.
I attempted to inbed the YEARFRAC formula for age into an IF statement and it did not work. Here's my invalid formula: =IF(INT(YEARFAC(G4,TODAY())),'Minor', )
Dear Sandy,
(1) Kindly specify the age limit for declaring a person as 'Minor'.
(2) Also mention if it is to be considered from 1Jan of any year or as per the current date.
When posting a question, please be very clear and concise.
Vijaykumar Shetye, Goa, India
Hi there
I'm trying to create a spreadsheet in excel that will highlight when a supervision is due
For example - I want cell B2 to change to red 60 days after 01/01/2016
I then want cell C2 to change to red 120 days after 01/01/2016
I've been looking for a conditional format for weeks and am struggling
Any help would be great
Dear J Rogers,
Select cell B2,
Go to Home - Conditional Formatting - New rule - Use a Formula to determine which cell to format - Format values where this formula is true
Enter the formula
=B2+60
Go to format - Fill
Select Red colour
Click OK twice
Repeat the same for C2 and other cells
The formula for C2 would be
=B2+120
Vijaykumar Shetye, Goa, India
how to + n _ date format in excel 12/11/2016
10/12/2011
Hi,
I am calculating pension between two dates on yearly basis for staff whom are leaving the organization. I could use this formula to do that but, the employee is doing the calculation manually so it shows slight difference.
Can anyone help please!
=IF((YEAR(G13)<YEAR(TODAY())),(P27/365*(U13-DATE(YEAR(TODAY()),1,1))))
G13 is joining year;
P27 is current salary;
U13 is separation date;
DATE(YEAR(TODAY()),1,1))))is January 1, of each year;
The Salary is AFS 20,000.00 per month;
Based on this formula the pension amount from January 1, 2016 to May 31, 2016 is AFA 8,273.97 and based on manual calculation it is =AFA 20000/12*5= AFA 8,333.33 and difference is AFA 59.36
Your soonest rely will be highly appreciated!
Ahmad
I would like to know if there is a formula that can continuously calculate dates: patients at a clinic are reviewed every 13 weeks from their admission date, with no set time for stopping the reviews. I would like a column to show the next review date, after the current review date has been passed. It will be a tool used so nobody misses out on their review (Dates here are dd/mm/yyyy).
For example:
Patient Name Admission Date Next Review Date
John Doe 03/03/2016 02/06/2016
Jane Doe 18/05/2014 14/08/2016
Thank you.
Dear Jess,
If the admission date is in B2, then use the formula in C2 and drag it down.
=B2+13*7 or
=B2+91
Change the format of the cell to the required date format.
Vijaykumar Shetye, Goa, India
Hello, I would like to know how to calculate the requested room nights per day, for example to achieve the given budget for the month.
thank you