3 ways to add days to excel date (Easy Formula)

You could have encountered circumstances where you wanted to extend a date by a specific number of days. Let's say your business only permits you to send an ordered item out for delivery after a specific number of days. Alternatively, you may have a payroll system that only permits you to distribute a person's paycheck following a certain security period. As a result, you will need to determine the date that each employee's salary will be sent and record it in a different column. It is impossible to manually determine the delivery date for each row of your data in these instances. Thankfully, it has offered a simple method for you to add a particular number of days to a date.

These are the three methods for adding days to a date in Excel.

Using Simple Formulas to Add Days to Date in Excel

Users can use formulas in Excel to calculate new dates by adding days or weeks to a given date. These methods are simple and convenient for working with dates in Excel.

Formula 1: Date + Number

A Simple Method to Add Days to a Date in Excel with Arithmetic Functions.

Example

We can use the same dataset as in the previous example, but this time we do not have a column for the number of days for delivery. We will assume that each item will be delivered in 15 days.

Step 1: Select an empty cell and type an Equal sign (=) to start the formula and then click on the cell that has the date (F2).

Step 2: Then add a Plus sign (+) and type “15”. Press “Enter” and Excel will calculate the delivery date by adding 15 days to the order date.

Step 3: Copy the formula to the other cells by dragging the “Autofill” handle.

Formula 2: Cell location

The cell location method is similar to the arithmetic function method, but it does not require a separate column for the number of delivery days. Let’s see how we can use this method to calculate new dates easily.

Example

Suppose we have an Electronics Store that receives orders from all over the U.S. We can use a simple arithmetic operator to calculate the Delivery Date by adding the number of days for delivery to the date the order was placed.

Step 1: Select an empty cell where you want to display the delivery date. For example, we will use cell E2 in the Delivery Date column.

Step 2: Type an Equal sign, “=”, to start the formula and then click on the cell that has the Date (C2).

Step 3: Add a Plus sign, “+”, as the operator, and then click on the cell that has the number of days to add (F2).

Step 4: Press “Enter” and Excel will show the delivery date in the selected cell. You can copy the formula to the other cells by dragging the “Autofill” handle.

These methods are very simple and easy to use to add days to a date in Excel. If you want to make things even simpler, you can try using WPS Office, a free and user-friendly Excel alternative that lets you perform calculations for free.

Add Months/Years to Date in Excel: Using the DATE Function

The DATE function in Excel allows users to create or manipulate dates by specifying the year, month, and day. The syntax for the DATE function is: =DATE(year, month, day)

Calculate Month

We can use the same example as before, where we are calculating the delivery date. Our customers have asked us to deliver their orders 2 months from now. We can use the DATE or EDATE function to do this, here’s how:

Step 1: Select an empty cell and type the DATE function starting with the equal sign.

Step 2: In our first argument, year, we will enter the year the order was placed.

Step 3: For our second argument, month, we will enter the month of the order date plus the number of months that we want to add.

Step 4: Finally, we will enter the last argument, day, which is the day of the order date.

Step 5: Press “Enter” and the date with the number of months added will be displayed.

We can also use the EDATE function, which has only two arguments.

Step 1: For our first argument, we will select the cell that contains the order date and for the second argument, we will enter the number of months that we want to add.

Step 2: Hit “Enter” to allow Excel to execute the EDATE Function.

Both methods are effective. The DATE function allows us to add not only months but also days or years in a similar way. The EDATE function is simpler and quicker to add months to a date.

Calculate Year

We can use the same method as before using the DATE formula to add years to a date in Excel. Using the same example as above, let’s see how we can add 2 years to the current date.

Step 1: Choose an empty cell and enter the DATE function.

Step 2: For the first argument, year, we will enter the year of the order date plus 2.

Alt text: Excel cell with the DATE Function and the order year

Step 3: For the second and third arguments, month and day, we will enter the same values as the order date.

Step 4: Press “Enter” on your keyboard for the results.

The DATE function can help us with various project management activities and other real-life situations where we need to deal with dates. It can prevent us from doing manual calculations that could cause human mistakes.

DATE Function not working

If you find that the DATE formula to add days to date in Excel is not working properly, you can check the following things:

• Make sure that you have entered the correct syntax for the DATE function, which is =DATE(year, month, day).

• Make sure that you have used parentheses around the arguments of the DATE function, such as =DATE(2020, 1, 1).

• Make sure that you have entered valid values for the year, month, and day arguments, such as positive integers or cell references.

• Make sure that you have formatted the cell as a date, such as by using the Format Cells dialog box or the Number group on the Home tab.

Best Alternative to Microsoft Office——WPS Office

WPS Office has earned a remarkable reputation in the office suite industry, gradually establishing itself as a formidable player in the field. Despite the daunting competition posed by Microsoft Office, WPS Office has managed to stand out by offering a comprehensive set of features. This includes the added convenience of PDF functionality, coupled with integrated AI tools that streamline tasks. And on top of that,  it's entirely free, requiring no upfront investment.

WPS Office ensures compatibility with Microsoft Office's suite of productivity tools, providing users with a blend of convenience and versatility.

Pros:

Some of its additional pros include as following:

• Compatibility with Microsoft Office

• Integrated AI-powered productivity tools

• Free of cost

• Advanced PDF capabilities

• Cloud sharing functionality

• Mobile-friendly design

• User-friendly interface

  Trustpilot4.8

  WPS Office- Free All-in-One Office Suite

  Free Download

  5,820,008 User

  

  Algirdas Jasaitis

  logo

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

FAQs

Q1. What is the calendar formula?

A calendar formula is a mathematical expression that can be used to calculate the day of the week for any given date. There are different ways to derive a calendar formula, but one common method is based on the following facts:

• 1 ordinary year = 365 days = (52 weeks + 1 day) or 52 weeks + 1 odd day.

• 1 leap year = 366 days = (52 weeks + 2 days) or 52 weeks + 2 odd days.

• 100 years = 76 ordinary years + 24 leap years = (76 x 1 + 24 x 2) odd days = 124 odd days.

• Number of odd days in 100 years = (124 % 7) = 5 odd days.

• Number of odd days in 200 years = (5 x 2) % 7 = 3 odd days.

• Number of odd days in 300 years = (5 x 3) % 7 = 1 odd day.

• Number of odd days in 400 years = (5 x 4 + 1) % 7 = 0 odd day.

Using these facts, we can find the number of odd days for any year by adding the odd days for the century, the year, and the month. Then we can use a table to match the number of odd days with the corresponding day of the week. For example, if we want to find the day of the week for January 1, 2023, we can use the following steps:

• The century is 2000, which has 3 odd days.

• The year is 23, which has (23 + floor(23/4)) % 7 = (23 + 5) % 7 = 28 % 7 = 0 odd day.

• The month is January, which has no odd day.

• The total number of odd days is (3 + 0 + 0) % 7 = 3 % 7 = 3.

According to the table below, the number 3 corresponds to Wednesday.

Q2. How do you calculate extra days in a year?

To calculate the extra days in a year, you need to know the concept of odd days. An odd day is the extra day left after dividing the number of days in a year by 7. For example, a normal year has 365 days, which is 52 weeks and 1 odd day. A leap year has 366 days (52 weeks and 2 odd days). The number of odd days in a year determines how much the calendar shifts from one year to the next.

To find the number of odd days in a year, you can use the following formula:

• If the year is divisible by 4, but not by 100, or if the year is divisible by 400, then it is a leap year and has 2 odd days.

• Otherwise, it is a normal year and has 1 odd day.

Mastering Excel Formulas: Adding Days to Dates with Precision

In this guide, we've thoroughly explored three Excel formulas to add days to date. By mastering these techniques, you'll handle date-related tasks seamlessly, ensuring accurate data representation. WPS Spreadsheet is an excellent tool to effortlessly execute these operations attributing to how user-friendly it is. Enhance your skills today and download WPS Office for lasting proficiency.