![]() Add months to date: =DATE(YEAR(A2), MONTH(A2) + 2, DAY(A2)).The real formulas could look similar to these: =DATE(YEAR( date), MONTH( date) + N months, DAY( date)) =DATE(YEAR(A2), MONTH(A2) - $C$2, DAY(A2))Īnd of course, you can type the number of month to add or subtract in the formula instead of referring to a cell: ![]() Naturally, nothing prevents you from typing the minus sign directly in the formula to subtract months from a date: Yep, it's that simple :) If you type a negative number in C2, the formula will subtract months instead of adding them: the month of the date in A2 + the number of months you specified in cell C2, and.The DATE(year, month, day) function takes the following arguments: The logic behind the formula is obvious and straightforward. Now, let's see what the function is actually doing. 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.Įnter 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: Add months to a date with Excel DATE function 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. How to add / subtract months to date in Excel To subtract 2 weeks from today's date, you write =TODAY()-2*7. The general formula to add a specified number of days to a date in as follows: 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. How to subtract or add days to date in Excel 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. 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. 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. 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 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.įor instance, the following formula subtracts 1 from 2 and returns the difference of 5 days: Subtracting dates with Excel DATE function 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:Įxample 4. Just use the TODAY() function instead of date 1: To subtract a date from today's date, you can employ either of the above formulas. ![]() 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.Įxample 3. When you subtract a more recent date () from an earlier date (), the subtraction operation returns a negative number (-5) exactly as it should. The following screenshot demonstrates that both calculations return identical results, except for row 4 where the DATEDIF function returns the #NUM! error. If the above formula looks too plain, you can achieve the same result in a guru-like way by using Excel's DATEDIF function: Subtract dates using Excel DATEDIF function So, you are actually subtracting two numbers, and an ordinary arithmetic operation works without a hitch:Įxample 2. Subtract one date from the other directlyĪs you probably know, Microsoft Excel stores each date as a unique serial numbers beginning with 1 that represents January 1, 1900. As is often the case in Excel, the same result can be achieved in several ways. 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.
0 Comments
Leave a Reply. |