WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free download

Windows • MacOS • Linux • iOS • Android

banner

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

July 21, 2022
2.7K Views

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.

Excel Spreadsheet with example dataset



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).

Excel Spreadsheet with formula in a cell



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.

Excel Spreadsheet with formula to add 15 to a cell value



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

Excel Spreadsheet with results


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.

Excel example dataset



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).

Excel spreadsheet with a formula in cell E2



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

Excel spreadsheet with number of days added to date



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.

Excel spreadsheet with delivery dates calculated in column G



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.

Excel cell with the DATE function



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

Excel cell with the DATE function and the year argument



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.

Excel cell with the DATE function and the month argument



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

Excel cell with the DATE function and the day argument



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

Excel cell with the DATE function results



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

Excel cell with the EDATE function



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.

Excel cell with the EDATE function and its arguments



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

Excel cell with the EDATE function results



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.

Excel cell with 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.

Excel cell with the DATE Function arguments



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

Excel cell with the DATE Function 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



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

    Trustpilotstars4.8
    WPS Office- Free All-in-One Office Suite
    5,820,008 User
    avator
    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.

Number

Day

0

Sunday

1

Monday

2

Tuesday

3

Wednesday

4

Thursday

5

Friday

6

Saturday

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.


15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.