How To Add Subtract Dates In Excel 2022

How to Add/Subtract Dates in Excel – Guide

In this article, you will learn how to add and subtract dates in Excel using various formulas including subtracting two dates, adding days, weeks, months and years to a date and more. If you’ve been following our Excel date courses, you’re already familiar with a variety of formulas for calculating different time units such as days, weeks, months, and years. You will almost certainly do some math with the dates in your worksheets as you study them. This Excel article walks you through some formulas for adding and subtracting dates that might be helpful.

How to subtract dates in excel

Assuming you have two dates in cells A2 and B2, and now you want to subtract one date from the other to find out how many days are between those dates. As is often the case in Excel, the same result can be achieved in several ways.

Example 1. Subtract one date from another directly

As you probably know, Microsoft Excel stores each date as a unique serial number starting with 1 which represents January 1, 1900. So you are actually subtracting two numbers and a common arithmetic operation works without a hitch: =B2-A2

Example 2. Subtract dates using the Excel DATEDIF function

If the above formula seems too simple, you can get the same result in a guru-like manner using Excel’s DATEDIF function: =DATASE(A2, B2, “d”) The following screenshot demonstrates that both calculations return identical results, except for line 4, where the DATEDIF function returns the #NUM error. Let’s find out why this happens. When you subtract a more recent date (May 6, 2015) from an earlier date (May 1, 2015), the subtraction operation returns a negative number (-5) just as it should. The syntax of the Excel DATEDIF function, however, does not allow the start date to be greater than the date final and therefore returns an error.

Example 3. Subtract a date from the current date

To subtract a date from today’s date, you can employ one of the formulas above. Just use the TODAY() function instead of date 1: =TODAY()-A2 or =DATESE(A2,TODAY(), “d”) As 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 the Excel DATE function

If you prefer to provide the dates directly in the formula, enter each date using the DATE(year, month, day) function and subtract one date from the other. For example, the following formula subtracts May 15, 2015 from May 20, 2015 and returns the difference of 5 days: =DATE(2015, 5, 20) – DATE(2015, 5, 15) Wrapper upWhen it comes to subtracting dates in Excel and you want to find out how many days are between two dates, it makes sense to use the easiest and most obvious option – just subtract one date directly from the other. If you want to count the number of months or years between two dates, the DATEDIF function is the only possible solution and you will find some example formulas in the next article that will cover this function in detail. now that you know how to subtract two dates, let’s see How to add or subtract days, months or years from a given date. There are several 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 for adding a specified number of days to a date is as follows: Date + N days The date can be entered in several ways: The following screenshot demonstrates the above formulas in action. The current date at the time of writing is May 6, 2015:

Example 2. Subtracting days from a date in Excel

To subtract a given number of days from a given date, you re-run the usual arithmetic operation. The only difference from the previous example is that you type the minus sign instead of the plus 🙂 Date – N days Here are some example formulas:

How to add or subtract weeks to date

In case you want to add or subtract whole weeks to a given date, you can use the same formulas to add/subtract days, and simply multiply the number of weeks by 7: Adding weeks to a date in Excel: cell + N weeks * 7 For example, you add 3 weeks to the date in A2, use the following formula: =A2+37. Subtracting weeks from date in Excel: cell – N weeks * 7 To subtract 2 weeks from today’s date, type =TODAY()-27.

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 the DATE or EDATE function, as shown below.

Example 1. Add months to a date with Excel’s DATE function

Taking a list of dates in column A, for example, enter the number of dates you want to add (positive number) or subtract (negative number) into some cell, say C2. Enter the following formula in cell B2 and drag the cell corner all the way 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: Yes, it’s that simple 🙂 If you enter a negative number in C2, the formula will subtract months instead of adding them: Of course, nothing prevents you from typing the minus sign directly into the formula for subtracting months from a date: =DATE(YEAR(A2), MONTH(A2) – $C$2, DAY(A2)) And of course, you can type the month number to add or subtract in the formula instead of referring to a cell: =DATE(YEAR(date), MONTH(date) + N months, DAY(date)) The actual formulas might look like these:

Example 2. Adding or subtracting 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 modern versions of Excel 2007, 2010, 2013 and the upcoming Excel 2016. In your EDATE(start_date, months) formulas, you provide the following 2 arguments: The following formula used in our date column produces 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 month number to add/subtract directly in the formula. Dates must be entered using the DATE function or as the results of other formulas. For example: Observation. The Excel EDATE function returns a serial number representing the date. To force Excel to display it as a date, you must apply the Date format to cells with your EDATE formulas. See Changing the Date Format in Excel for 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: DATE(YEAR(date) + N years, MONTH(date), DAY(date)) In your Excel worksheet, formulas might look like this: If you enter the year number 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’ve carefully looked at the previous two examples, I think you’ve already guessed. how to add or subtract a combination of years, months and days to a date in a single formula. Yes, using the good old DATE function 🙂 To add years, months, days: DATE(YEAR(date) + X years, MONTH(date) + Y months, DAY(date) + Z days) To subtract years, months, days: DATE(YEAR(date) – X years, MONTH(date) – Y months, DAY(date) – Z 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 date column, the formula takes the following form: =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 allows you to operate in units of time (hours, minutes and seconds) in exactly the same way you handle years, months and days with the DATE function. To add time in Excel: cell + TIME(hours, minutes, seconds) To subtract time in Excel: cell – TIME(hours, minutes, seconds) 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 + HOUR(2, 30, 15) If you want to add and subtract time units within a 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 units you want to change in some cells and refer to those cells in your formula: =A2 + TIME($C$2, $D$2, $E$2) If the original cells contain date and time, the above formula will also work perfectly:

Final note

I hope you like the guide How to Add/Subtract Dates in Excel. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.