How to add to date

Add or subtract dates

Suppose you want to adjust a project’s schedule date by adding two weeks to see what the new completion date will be, or you want to determine how long a single activity will take to complete in a list of project tasks. You can add or subtract a number of days to or from a date by using a simple formula, or you can use worksheet functions that are designed to work specifically with dates in Excel.

Add or subtract days from a date

Suppose that a bill of yours is due on the second Friday of each month. You want to transfer funds to your checking account so that those funds arrive 15 calendar days before that date, so you’ll subtract 15 days from the due date. In the following example, you’ll see how to add and subtract dates by entering positive or negative numbers.

Enter your due dates in column A.

Enter the number of days to add or subtract in column B. You can enter a negative number to subtract days from your start date, and a positive number to add to your date.

In cell C2, enter =A2+B2, and copy down as needed.

Add or subtract months from a date with the EDATE function

You can use the EDATE function to quickly add or subtract months from a date.

The EDATE function requires two arguments: the start date and the number of months that you want to add or subtract. To subtract months, enter a negative number as the second argument. For example, =EDATE(«9/15/19»,-5) returns 4 /15/19.

For this example, you can enter your starting dates in column A.

Enter =EDATE(A2,B2) in cell C2, and copy down as needed.

Depending on the format of the cells that contain the formulas that you entered, Excel might display the results as serial numbers. For example, 8-Feb-2019 might be displayed as 43504.

Excel stores dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2010 is serial number 40179 because it is 40,178 days after January 1, 1900.

If your results appear as serial numbers, select the cells in question and continue with the following steps:

Press Ctrl+1 to launch the Format Cells dialog, and click the Number tab.

Under Category, click Date, select the date format you want, and then click OK. The value in each of the cells should appear as a date instead of a serial number.

Add or subtract years from a date

In this example, we’re adding and subtracting years from a starting date with the following formula:

How the formula works:

The YEAR function looks at the date in cell A2, and returns 2019. It then adds 3 years from cell B2, resulting in 2022.

The MONTH and DAY functions only return the original values from cell A2, but the DATE function requires them.

Finally, the DATE function then combines these three values into a date that’s 3 years in the future — 02/08/22.

Add or subtract a combination of days, months, and years to/from a date

In this example, we’re adding and subtracting years, months and days from a starting date with the following formula:

How the formula works:

The YEAR function looks at the date in cell A2, and returns 2019. It then adds 1 year from cell B2, resulting in 2020.

The MONTH function returns 6, then adds 7 to it from cell C2. This gets interesting, because 6 + 7 = 13, which is 1-year and 1-month. In this case, the formula will recognize that and automatically add another year to the result, bumping it from 2020 to 2021.

The DAY function returns 8, and adds 15 to it. This will work similarly to the MONTH portion of the formula if you go over the number of days in a given month.

The DATE function then combines these three values into a date that is 1 year, 7 months, and 15 days in the future — 01/23/21.

Here are some ways you could use a formula or worksheet functions that work with dates to do things like, finding the impact to a project’s schedule if you add two weeks, or time needed to complete a task.

Let’s say your account has a 30-day billing cycle, and you want to have the funds in your account 15 days before the March 2013 billing date. Here’s how you would do that, using a formula or function to work with dates.

In cell A1, type 2/8/13.

In cell B1, type =A1-15.

In cell C1, type =A1+30.

In cell D1, type =C1-15.

Add months to a date

We’ll use the EDATE function and you’ll need the start date and the number of months you want to add. Here’s how to add 16 months to 10/24/13:

In cell A1, type 10/24/13.

In cell B1, type =EDATE(A1,16).

To format your results as dates, select cell B1. Click the arrow next to Number Format, > Short Date.

Subtract months from a date

We’ll use the same EDATE function to subtract months from a date.

Here, we’re specifying the value of the start date entering a date enclosed in quotation marks.

You can also just refer to a cell that contains a date value or by using the formula =EDATE(A1,-5)for the same result.

How to add and subtract dates, days, weeks, months and years in Excel

by Svetlana Cheusheva | updated on July 15, 2021

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 days, 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:

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:

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:

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:

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:

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:

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:

Subtracting weeks from date in Excel:

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:

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:

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:

And of course, you can type the number of month to add or subtract in the formula instead of referring to a cell:

The real formulas could look similar to these:

Example 2. Add or subtract months to a date with Excel EDATE

In your EDATE(start_date, months) formulas, you supply the following 2 arguments:

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:

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:

=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:

Applied to our column of dates, the formula takes the following shape:

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:

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:

If the original cells contain both date and time, the above formula works perfectly too:

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:

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:

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.

DateTime::add

(PHP 5 >= 5.3.0, PHP 7, PHP 8)

Description

Adds the specified DateInterval object to the specified DateTime object.

The procedural version takes the DateTime object as its first argument.

Parameters

Return Values

Returns the modified DateTime object for method chaining or false on failure.

See Also

User Contributed Notes 12 notes

Another simple solution to adding a month but not autocorrecting days to the next month is this.
(Also works for substracting months)

$dt = new DateTime(«2016-01-31»);

Hope this helps someone.

Here is a solution to adding months when you want 2014-10-31 to become 2014-11-30 instead of 2014-12-01.

/**
* Class MyDateTime
*
* Extends DateTime to include a sensible addMonth method.
*
* This class provides a method that will increment the month, and
* if the day is greater than the last day in the new month, it
* changes the day to the last day of that month. For example,
* If you add one month to 2014-10-31 using DateTime::add, the
* result is 2014-12-01. Using MyDateTime::addMonth the result is
* 2014-11-30.
*/
class MyDateTime extends DateTime
<

$TodaySQL = substr(date(DATE_ISO8601 ),0,10)
$LastYearSQL = date(‘Y.m.d’,strtotime(«-1 years»))
$NextMonthEndSQL = date(‘Y.m.d’,strtotime(«+1 months»))

// handy little SQL date formats

//Today
2021-03-24
//Last year
2020.03.24
//Next month
2021.04.24

If you need add() and sub() that don’t modify object values, you can create new methods like this:

class DateTimeEnhanced extends DateTime <

$interval = DateInterval :: createfromdatestring ( ‘+1 day’ );

What you can do with this function/method is a great example of the philosophy: «just because you can do it doesn’t mean you should». I’m talking about two issues: (1) the number of days in the month which varies from months 1-12 as well as for month 2 which could be leap year (or not); and then issue (2): what if there is the need to specify a large quantity of an interval such that it needs to be re-characterized into broader-scoped intervals (i.e. 184 seconds ==> 3 minutes-4 seconds). Examples in notes elsewhere in the docs for this function illustrate both issues and their undesired effects so I won’t focus on them further. But how did I decide to handle? I’ve gone with four «public» functions and a single «private» function, and without giving you a bunch of code to study, here are their summaries.

**Results/goals.
—any number of days/hours/minutes/seconds can be passed in to add/subtractTime and all of «Y/M/D/H/M/S» values get adjusted as you would expect.
—using adjustYear/Month lets you pass +/- values and only «Y/M» values get modified without having undesirable effects on day values.
—a call to the «recharacterize» function helps ensure proper and desired values are in the intervals prior to calling date_add to let it do its work.

/* results:
1383458399 1383458399 2013-11-03 01:59:59 EDT
1383458400 1383462000 2013-11-03 02:00:00 EST

noticed how the second column went from 1383458399 to 1383462000 even though only 1 second was added?
*/

Remark, that calculations on date are not defined as bijective operations. The Summertime is integrated by mixing two concepts. You should test it beforehead.

Datetime will correct a date after each summation, if a date (29.2.2021 => 1.3.2021) or a datetime (29.3.2020 2:30 am (Europe/Berlin) => 29.3.2020 3:30 or 29.3.2020 1:30)

If you use fraction of seconds, you may have surprises. It only occurs when the sum of the floating point parts results in exactly 1 second (0.5 + 0.5 ou 0.3 + 0.7, for example). See these cases at intervals slightly bigger than 1 second:

To resolve, add 1 second to the interval and f property must be negative (-1.0 plus original value):

Be careful when using this function, I may have happened upon a bug in PHP7.

My code is as follows

//get date from post or else fill with today’s date
if (isset($_POST[«from»]))
<
$from = date_create($_POST[«from»]);
>else<
$from = date_create(date(«Y-m-d»));
>

The resultant output is
$from = 2015-12-11
$to = 2015-12-11

In actuality the result should be
$from = 2015-12-10
$to = 2015-12-11

to fix this i needed to change the code to

//get date from post or else fill with today’s date
if (isset($_POST[«from»]))
<
$from = date_create($_POST[«from»]);
>else<
$from = date_create(date(«Y-m-d»));
>

This isn’t strictly the code I wanted. Possible bug?

Add Days (or Minutes, or Hours) to Javascript Date [or Subtract]

This article will explain how to add (or subtract) days, minutes, hours, seconds (etc.) to a JavaScript date.

Why would you want to do this? Perhaps you want to tell a user that their assignment is due in a week and provide the date, or maybe your application needs to know what the date was 30 days ago to pop up a reminder – there are near-infinite usage cases.

Add Days to Date Using Vanilla JavaScript

First, here’s how it’s done in plain old JavaScript:

The resulting date will be today’s date plus 4 days, 3 hours, 2 minutes, and 1 second.

You can supply negative numbers or use the – (subtraction) operator instead of addition.

Add Days to Date Using Moment.js

If you build applications that deal with dates frequently, Moment.js is invaluable.

Moment.js provides the tools to manage dates, timezones, time periods (the period between two dates) – all conveniently wrapped in easily used classes. The documentation is great, and it simplifies date handling while making it more reliable.

For example, to add 3 days and 2 minutes to the current date you’d use:

Which is far easier to read and less mistake-prone than doing it in vanilla JavaScript.

SqlHints.com

By Basavaraj Biradar

How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server

In this article we will discuss on How to add Days, Weeks, Months, Quarters or Years to a Date in Sql Server?

You may also like to read the following other popular articles on Date and Time in Sql Server:

How to add Days to DateTime in Sql Server?

We can use DATEADD() function like below to add days to DateTime in Sql Server. DATEADD() functions first parameter value can be day or dd or d all will return the same result. Below example shows how we can add two days to Current DateTime in Sql Server:

RESULT:

Alternatively, we can add Days to Date like below in Sql Server. In the below example we are add adding 2 days to a DateTime.

RESULT:

How to add Weeks to DateTime in Sql Server?

We can use DATEADD() function to add weeks to DateTime in Sql Server. DATEADD() functions first parameter value can be week or wk or ww, all will return the same result. Below example shows how we can add two weeks to Current DateTime in Sql Server:

RESULT:

How to add Months to DateTime in Sql Server?

We can use DATEADD() function like below to add Months to DateTime in Sql Server. DATEADD() functions first parameter value can be month or mm or m, all will return the same result. Below example shows how we can add two months to Current DateTime in Sql Server:

RESULT:

How to add Quarters to DateTime in Sql Server?

We can use DATEADD() function like below to add Quarters to DateTime in Sql Server. DATEADD() functions first parameter value can be quarter or qq or q, all will return the same result. Below example shows how we can add two months to Current DateTime in Sql Server:

RESULT:

How to add Years to DateTime in Sql Server?

We can use DATEADD() function like below to add Years to DateTime in Sql Server. DATEADD() functions first parameter value can be year or yyyy or yy, all will return the same result. Below example shows how we can add two Years to Current DateTime in Sql Server:

RESULT: