by In this tutorial, you will find a variety of useful formulas to add and subtract dates in Excel, such as subtracting two dates, adding days, weeks, months and years to a date, and more.
If you have been following our tutorials to working with dates in Excel, you already know an array of formulas to calculate different time units such as weekdays, weeks, months and years.
When analyzing the date information in your worksheets, you are likely to perform some arithmetic operations with those dates too. This tutorial explains a few formulas for adding and subtracting dates in Excel that you may find useful.
How to subtract dates in Excel
Supposing you have two dates in cells A2 and B2, and now you want to subtract one date from the other to know how many days are between these dates. As is often the case in Excel, the same result can be achieved in several ways.
Example 1. Subtract one date from the other directly
As you probably know, Microsoft Excel stores each date as a unique serial numbers beginning with 1 that represents January 1, 1900. So, you are actually subtracting two numbers, and an ordinary arithmetic operation works without a hitch:
=B2-A2
Example 2. Subtract dates using Excel DATEDIF function
If the above formula looks too plain, you can achieve the same result in a guru-like way by using Excel's DATEDIF function:
=DATEDIF(A2, B2, "d")
The following screenshot demonstrates that both calculations return identical results, except for row 4 where the DATEDIF function returns the #NUM! error. Let's figure out why that happens.
When you subtract a more recent date (6-May-2015) from an earlier date (1-May-2015), the subtraction operation returns a negative number (-5) exactly as it should. The syntax of the Excel DATEDIF function, however, does not allow the start date to be greater than the end date and therefore it returns an error.
Example 3. Subtract a date from the current date
To subtract a date from today's date, you can employ either of the above formulas. Just use the TODAY() function instead of date 1:
=TODAY()-A2
or
=DATEDIF(A2,TODAY(), "d")
Like in the previous example, both formulas work fine when today's date is greater than the date you are subtracting from it, otherwise DATEDIF fails:
Example 4. Subtracting dates with Excel DATE function
If you prefer to supply the dates directly in the formula, then enter each date using the DATE(year, month, day) function and then subtract one date from the other.
For instance, the following formula subtracts 15-May-2015 from 20-May-2015 and returns the difference of 5 days:
=DATE(2015, 5, 20) - DATE(2015, 5, 15)
Wrapping up, when it comes to subtracting dates in Excel and you want to find out how many days are between two dates, it makes sense to go with the easiest and most obvious option - simply subtract one date directly from another.
If you are looking to count the number of months or years between two dates, then the DATEDIF function is the only possible solution and you will find a few formula examples in the next article that will cover this function in full details.
Now that you know how to subtract two dates, let's see how you can add or subtract days, months, or years to a given date. There are a number of Excel functions suitable for this purpose, and which one you use depends on which unit you want to add or subtract.
How to subtract or add days to date in Excel
If you have a date in some cell or a list of dates in a column, you can add or subtract a certain number of days to those dates using a corresponding arithmetic operation.
Example 1. Adding days to a date in Excel
The general formula to add a specified number of days to a date in as follows:
The date can be entered in several ways:
- As a cell reference, e.g.
=A2 + 10 - Using the DATE(year, month, day) function, e.g.
=DATE(2015, 5, 6) + 10 - As a result of another function. For example, to add a given number of days to the current date, use the TODAY() function:
=TODAY()+10
The following screenshot demonstrates the above formulas in action. The current date at the moment of writing was 6 May, 2015:
Note. The result of the above formulas is a serial number representing the date. To get it displayed as a date, select the cell(s) and press Ctrl+1 to open the Format Cells dialog. On the Number tab, select Date in the Category list, and then choose the date format you want. For the detailed steps, please see How to change date format in Excel.
Example 2. Subtracting days from a date in Excel
To subtract a given number of days from a certain date, you perform a usual arithmetic operation again. The only difference from the previous example is that you type the minus sign instead of plus :)
Here are a few formula examples:
=A2-10=DATE(2015, 5, 6)-10=TODAY()-10
How to add or subtract weeks to date
In case you want to add or subtract whole weeks to a certain date, you can use the same formulas as for adding / subtracting days, and simply multiply the number of weeks by 7:
Adding weeks to a date in Excel:
For example, you add 3 weeks to the date in A2, use the following formula: =A2+3*7.
Subtracting weeks from date in Excel:
To subtract 2 weeks from today's date, you write =TODAY()-2*7.
How to add / subtract months to date in Excel
If you want to add or subtract a certain number of whole months to a date, you can employ either the DATE or EDATE function, as demonstrated below.
Example 1. Add months to a date with Excel DATE function
Taking a list of dates in column A for example, type the number of dates you want to add (positive number) or subtract (negative number) in some cell, say C2.
Enter the following formula in cell B2 and then drag the corner of the cell all the way down to copy the formula to other cells:
=DATE(YEAR(A2), MONTH(A2) + $C$2, DAY(A2))
Now, let's see what the function is actually doing. The logic behind the formula is obvious and straightforward. The DATE(year, month, day) function takes the following arguments:
- the year of the date in cell A2;
- the month of the date in A2 + the number of months you specified in cell C2, and
- the day of the date in A2.
Yep, it's that simple :) If you type a negative number in C2, the formula will subtract months instead of adding them:
Naturally, nothing prevents you from typing the minus sign directly in the formula to subtract months from a date:
=DATE(YEAR(A2), MONTH(A2) - $C$2, DAY(A2))
And of course, you can type the number of month to add or subtract in the formula instead of referring to a cell:
=DATE(YEAR(date), MONTH(date) + N months, DAY(date))
The real formulas could look similar to these:
- Add months to date:
=DATE(YEAR(A2), MONTH(A2) + 2, DAY(A2)) - Subtract months from date:
=DATE(YEAR(A2), MONTH(A2) - 2, DAY(A2))
Example 2. Add or subtract months to a date with Excel EDATE
Microsoft Excel provides a special function that returns a date that is a specified number of months before or after the start date - the EDATE function. It is available in all versions of Excel 2007 and higher.
In your EDATE(start_date, months) formulas, you supply the following 2 arguments:
- Start_date - the start date from which to count the number of months.
- Months - the number of months to add (a positive value) or subtract (a negative value).
The following formula used on our column of dates yields exactly the same results as the DATE function in the previous example:
When using the EDATE function, you can also specify the start date and the number of month to add / subtract directly in the formula. Dates should be entered by using the DATE function or as results of other formulas. For example:
- To add months in Excel:
=EDATE(DATE(2015,5,7), 10)The formula adds 10 months to 7-May-2015.
- To subtract months in Excel:
=EDATE(TODAY(), -10)The formula subtracts 10 months from today's date.
Note. The Excel EDATE function returns a serial number representing the date. To force Excel to display it as a date, you should apply the Date format to the cells with your EDATE formulas. Please see Changing the date format in Excel for the detailed steps.
How to subtract or add years to date in Excel
Adding years to a date in Excel is done similarly to adding months. You use the DATE(year, month, day) function again, but this time you specify how many years you want to add:
In your Excel worksheet, the formulas may look as follows:
- To add years to a date in Excel:
=DATE(YEAR(A2) + 5, MONTH(A2), DAY(A2))The formula adds 5 years to the date in cell A2.
- To subtract years from a date in Excel:
=DATE(YEAR(A2) - 5, MONTH(A2), DAY(A2))The formula subtracts 5 years from the date in cell A2.
If you type the number of year to add (positive number) or subtract (negative number) in some cell and then refer to that cell in the DATE function, you will get a universal formula:
Add / subtract days, months and years to date
If you carefully observed the two previous examples, I think you have already guessed how to add or subtract a combination of years, months and days to a date in a single formula. Yep, using the good old DATE function :)
To add years, months, days:
To subtract years, months, days:
For example, the following formula adds 2 years, 3 months and subtracts 15 days from a date in cell A2:
=DATE(YEAR(A2) + 2, MONTH(A2) + 3, DAY(A2) - 15)
Applied to our column of dates, the formula takes the following shape:
=DATE(YEAR(A2) + $C$2, MONTH(A2) + $D$2, DAY(A2) + $E$2)
How to add and subtract times in Excel
In Microsoft Excel, you can add or subtract times using the TIME function. It lets you operate on time unites (hours, minutes and seconds) exactly in the same way as you handle years, months and days with the DATE function.
To add time in Excel:
To subtract time in Excel:
Where A2 contains the time value you want to change.
For example, to add 2 hours, 30 minutes and 15 seconds to the time in cell A2, you can use the following formula:
=A2 + TIME(2, 30, 15)
If you want to add and subtract time unites within one formula, just add the minus sign to the corresponding values:
=A2 + TIME(2, 30, -15)
The above formula adds 2 hours and 30 minutes to the time in cell A2 and subtracts 15 seconds.
Alternatively, you can enter the time unites you want to changes in some cells, and refer to those cells in your formula:
=A2 + TIME($C$2, $D$2, $E$2)
If the original cells contain both date and time, the above formula works perfectly too:
Date & Time Formula Wizard - quick way to add and subtract dates in Excel
Now that you know a bunch of different formulas to calculate dates in Excel, wouldn't you want to have just one that can do all this? Of course, such a formula can never exist. However, there exists the Date & Time Wizard that can build any formula for you on the fly, provided that you have our Ultimate Suite installed in your Excel. Here's how:
- Select the cell in which you want to insert the formula.
- Head to the Ablebits Tools tab, and click the Date & Time Wizard button:
- The Date & Time Wizard dialog window shows up. Depending on whether you want to add or subtract dates, switch to the corresponding tab, supply data for the formula arguments, and click the Insert Formula button.
As an example, let's add a few months to the date in cell A2. For this, you go to the Add tab, type A2 in the Enter a date box (or click in the box and select the cell on the sheet), and type the number of months to add in the Month box.
The wizard makes a formula and shows its preview in the cell. It also shows the calculated date under Formula result:
If you are satisfied with the result, click the Insert formula button. Once the formula is added, you can copy it to as many cells as necessary:
That was quite a simple formula, wasn't it? Let's give the wizard something more challenging to work on. For example, let us subtract some years, months, weeks and days from the date in A2. To have it done, switch to the Subtract tab and type the numbers in the corresponding boxes. Or you can enter the units in separate cells and supply references to those cells, as shown in the screenshot below:
Clicking the Insert formula button inputs the following formula in A2:
=DATE(YEAR(A2)-D2,MONTH(A2)-E2,DAY(A2)-G2-F2*7)
If you plan to copy the formula to other cells, you have to change all cell references except A2 to absolute references so that the formula copies correctly (by default, the wizard always uses relative references). To fix the reference, you simply type the $ sign before the row and column coordinates, like this:
=DATE(YEAR(A2)-$D$2,MONTH(A2)-$E$2,DAY(A2)-$G$2-$F$2*7)
And get the following results:
Additionally, you can click the Show time fields link and add or subtract date and time units with one formula.
If you wish to play with the Date & Time Formula Wizard in your own worksheets, you are welcome to download the 14-day trial version of the Ultimate Suite.
This is how you add and subtract dates in Excel. I am hopeful you have learned a couple of useful functions today. I thank you for reading and hope to see you on our blog next week.
300 comments
Hi guys,
Please advise on below
how to modify the next formula formulation for the following:
If= required date less than 5 weeks,"check","no check)
how can I do in one excel box to show a week like
Jan 01, 2016 to Jan 07, 2016
Jan 08, 2016 to Jan 14, 2016
and so on
I can do manually but I want to do it in a formula.
Thanks
Joining Date last date Previous Experience Total Year Experience Grand Total Experience
25-May-14 1-Jan-16 5 years 3 months 1 years, 7 months, 7 days
7-Oct-14 31-Mar-16 4 years 2 months 1 years, 5 months, 24 days
21-Jan-15 24-Mar-16 3 years 9 months 1 years, 2 months, 3 days
please help how to add Previous Experience to Total year experience....
Sorry, it's hard to think about ways of getting data without seeing your table. If the "Previous Experience" column has a string value, you can try the following:
To get the number of years and months from Previous Experience you can use formulas like these:
=LEFT(D3, SEARCH(" ", D3, 1)) - for years
=MID(D3, SEARCH("years", D3, 1)+5, SEARCH("months", D3, 1) - (SEARCH("years", D3, 1)+ 5)) - for months
If the "Total Year Experience" column is countable, add the results to the formula for this column.
The formula "How to add / subtract months to date in Excel" have a gap with date 29
If I add 1 month from 29/01/2016 to 29/01/2017, the month of February gone missing.
I manually solve the question, but needed to go deeply. THANK YOU.
I want to add X number of days to the date is cell A2, showing the resulting date in cell B2. However, if I use =A2+1 (for example) to add one day to the date in A2, but no date has yet been entered in A2, then the display in B2 is "VALUE". Is there a way to not have that display, leaving B2 blank until a date is entered in A2, whereupon B2 would display the date according to the formula?
Hello, Rod.
Please try this formula:
=IF(NOT(ISBLANK(A2)), A2+1, "")
I need a formula to identify any help desk ticket created between today at 12:01am and a week ago at 12:01am.
So, in essence, I need to create a formula that says this:
(If Ticket_Opened_date is between (today at 12:01am - 7 days) and (today at 12:00am), "New", "Old").
The trouble is the part about it being at 12:01am and making sure the formula is dynamic so I don't have to recreate it each week. Note: Please trust me when I say I can't do it via the standard date filter options (so don't even bother suggesting that).
Can someone help?
Hello, Ruthie.
I think that the following formula should work for your task:
=IF(AND(D17>(NOW() - HOUR(NOW())/24 - MINUTE(NOW())/24/60 + 12/24 -7),D17>(NOW() - HOUR(NOW())/24 - MINUTE(NOW())/24/60 + 12/24 -7)), "New", "Old")
Just like the comment 3 above i need to calculate the differences between dates to get a format like 2yrs, 3months, 5days, 7hrs and 25min
for example difference between 20/10/2014 9:34 and 13/12/2016 17:20.to get
2years, 1month, 23days, 16hrs, 14min.
Hello, Bola,
Please try this formula:
=IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&" months ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days") &" " &TEXT(ABS(A2-B2), "h") & " hrs" &" " &TEXT(ABS(A2-B2), "mm:ss") & " min "
Hi,
I'm trying to create an If function that would evaluate a score, then based on that score value predict a future test date based off of the date the first test was taken. Here are two examples:
1.) TESTED: 1/1/2015 SCORE:>70 NEXT TEST:12 months from first date(1/1/2016) 2.) TESTED: 1/1/2015 SCORE:<70 NEXT TEST:6 months from first date(7/1/2016) Do you have any idea how to format this correctly? Every time I try, I end up with either an error, or the result of my text. Thanks!
Is there a way to calculate the number of years between four dates? I want to calculate years of employee service where there was a lapse in employment:
Start date 8/22/2005, End date 8/13/2008 = x years PLUS Start date 7/1/2009, End date 1/19/2010 = TOTAL YEARS. I tried adding two dated ifs together but it didn't work EG: =DATEDIF(A1,A2,"Y")+(DATEDIF(B1,B2,"Y"). (Answer s/b 3 years) Thx!
Hello, Barbara,
DATEDIF(A1,A2,"Y") returns the number of complete years. In your case 2+0 is 2. To get the correct result you need to sum 2 intervals and count the number of complete years using DateDIf:
=DATEDIF(0,(A2-A1+B2-B1),"Y")
How can I subtract in excel for below issue?
[d/m/y h:m AM/PM-d/m/y h:m AM/PM]and note that fri and sat will be excluded from the calculation
Hi Jules,
I assume that the data relating to dates must be a part of a greater data range. The solution to this is as follows:
Convert the data range to a Table using the Ctrl-T function. You will observe that all the column headings will have a drop-down filter. Click on the filter of the date column and select Date-filters - you will see a wide range of options. In excel 2013 there are 20 options with two more custom options.
I am sure that you will be able get the required results you need.
The specific requirement for this sort of filtering is the data in the date column should be entered in date format as per excel predefined formats.
Regards,
Ramki
Hi Irfan,
The simplest format I can assume from what you specify is as follows:
Step 1 - Cell A1 type in the "From" date.
Step 2 - Cell B1 type in the "To" date.
Step 3 - Cell C1 type in the fomula "=B1-A1+1. We add one to include the start date. You will then get the number of days.
Step 4 - In Cell D1 enter the formula to multiply Cell C1 by 24 to get the hours.
Step 5 - In Cell E1 multiply Cell D1 by 60 to get the number of minutes.
Note - If you enter the date function in Cell B1 as "=NOW()" then you will get the live time set in excel. This will assist you in getting the calculation of days and minutes, in decimal point, if you wish to.
Regards,
Ramki
Hi,
I have some issue to while calculating "FROM" "TO" example :
FROM 02/01/2016 11:14:13 AM
TO 05/01/2016 11:14:13 PM
How can I separate in above date
- How many Days
- How many Hrs.
- How many minutes
Thanks
You can follow below steps-
1. Formate cells- Select date formate as (MM/DD/YY TIME)(03/14/01 1:30PM) change for both cells.
2. Type below formula as it is-
=INT(B3-A3)&"Days,"& HOUR(B3-A3) &"hours,"&MINUTE(B3-A3)&"Minutes and"&SECOND(B3-A3)& D3"Second"
3.If your date is 1st Feb to 1st May then result as below-
From To Result
From To Result
2/1/16 11:14 AM 5/1/16 11:14 PM 90Days,12hours,0Minutes and0Second
4.And If your date is 2nd Jan to 5th Jan then result as below-
From To Result
1/2/16 11:14 AM 1/5/16 11:14 PM 3Days,12hours,0Minutes and0Second
Regards
Mangal
Hello,
I have a long list of records each with a date. I would like to filter my list to only show recent records dated today or within the last 21 days. I cannot find a way to do this despite reading lots of suggestions for date formulas. Please can you help?
Thanks, Jules
My query Cell
A1 - 01-jan-2016, i want to make
B1 - 11-jan-2016,
C1 - 21-jan-2016
D1 - 31-Jan-2016,
E1 - 01-Feb-2016,
F1 - 11-Feb-2016,
G1 - 21-Feb-2016,
H1 - 28-Feb-2016,
I1 - 01-Mar-2016.........
Or
A1 - 01-jan-2016, i want to make
B1 - 11-jan-2016,
C1 - 21-jan-2016
D1 - 31-Jan-2016,
E1 - 11-Feb-2016,
F1 - 21-Feb-2016,
G1 - 28-Feb-2016,
H1 - 10-Mar-2016.......
Hello,
how do I determine the difference between 2 dates, and also show a "0" if the start date is greater than the end date
Hi Mechele,
You can use a simple IF formula, for example:
=IF(A1>B1, 0, B1-A1)
Where A1 is the start date and B1 is the end date.
In a similar manner, you can use the DATEDIF function to calculate the difference between two dates in days, weeks, months or years:
=IF(A1>B1, 0, DATEDIF())
HELLO, I try count between two date (10-12-1990 & 5-1-2016) by use datedif in many computer the result some computer true (25 year 0 month 30 day) and another computer the result false (25 year 0 month 143 day) why the day (143) I cant find the place changed properties in excel to get to same first result.
.....thank for answer me.
Hello, Salah,
The problem may be caused by the difference in Date formats. Please send us your formula to check.
Hi
12/21/2015 8/15/1975
40-Yrs And 4-Months
=DATEDIF(E11,D11,"y")&"-Yrs"& " And " & DATEDIF(E11,D11,"ym")&"-Months"
May be helpful :
Interval-Meaning
m Months
d Days
y Years
ym Months Excluding Years
yd Days Excluding Years
md Days Excluding Years And Months
Thank you for your input, Jagadeesh!
Wow, thank you for sharing this. :)
Wow.... Wonderful. Thank u
It doesn't work with negative yrs/mos/days
Why doesn't it works with negative yrs/mos/days?
Nice Article.
How to subtract two dates like
12/21/2015 - 8/15/1975
How to get the answer to be 40 Yrs and 4 Months
Hello, Bobby,
Please have a look at Jagadeesh's reply in comment 3.