Adding days to a date helps determine deadlines. It can simplify various tasks at hand. For example, completing a task 10 days after the previous task. It is also helpful in stock management, to make sure you don’t sit with old stock.
4 Methods to add days to a date in Microsoft Excel:
- Add the quantity directly (If the number of days to be added are the same)
- Use a separate column (When the number of days to be added differs)
- Use the Paste Special option
- Use the End of Month formula
Let us begin.
Method #1: Add the Quantity Directly
This is applicable when the number of days to be added are the same.
This is the simplest way to add days to a date. It’s helpful if there is a database with a date field and we wish to add a particular number of days to a date.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add days.
Step #2: Select the cell
Click on the cell where you want to see the results of the formula. In our example, it’s the cell next to A2.
Step #3: Enter the Excel formula
The Excel formula is =Cell name + days
Call name would be where the date is you want to use, and days is the quantity of days you want to add.
For example: The delivery date of a product is 10 days after the purchase date. The purchase date is recorded in Column ‘A’. Therefore, the required formula in the adjacent cell will be:
=A2+10.
Step #4: Press enter
Press enter to get the result for the first entry in the Excel sheet.
Once done, click on the bottom right corner of the results (B2), where a little square will appear.
Hold the mouse button down, and drag the cursor down the remaining cells in Column B to calculate the other dates.
Only do this if all the days to be added are the same, i.e.,10 days as per our example.
Method #2: Use a Separate Column
This is applicable when the number of days to be added are different.
This method is more efficient if the quantity of days to be added are not the same.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add days.
Step #2: Select the cell
Click on the cell where you want to see the results of the formula.
In our example, it is B2 the cell next to A2.
Step #3: Enter the Excel formula
The Excel formula is =Cell name A + Cell name C
Cell name A contains the date you want to use, and Cell name C contains the number of days you want to add.
For example: Column A contains the purchase date, Column B will show the delivery date, and Column C contains the number of days to be added. Therefore, the formula in our example is:
=A2+C2.
Step #4: Press enter
Press enter to get the result for the first entry in the excel sheet.
Once done, click on the bottom right corner of the results (B2), where a little square will appear.
Hold the mouse button down, and drag the cursor down the remaining cells in Column B to calculate the other dates.
Method #3: Use the Paste Special Option
This is another way to add days to a date, especially when you do not want to add extra columns.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add days.
Step #2: In an adjacent cell, enter the number of days
Enter the number of days to be added in an adjacent cell and copy that cell.
You can right click and select Copy, or you can use Ctrl + C on your keyboard (Command + C for Mac users).
Step #3: Select the Paste Special option
Select the whole date column by clicking on the A right at the top, or by clicking and dragging over the contents of the column.
While keeping your cursor over this area, click the right mouse button, which will open a special menu.
Move your cursor down over the Paste Special option, which will open another submenu.
At the bottom of that submenu, there is another Paste Special option. Click on it.
Step #4: Add values
In this new Paste Special window, select Values in the Paste section and select Add in the Operation section.
Click on the OK button.
Step #5: The days are added
As you can see below, the dates have all increased by 2 days, without needing extra columns.
Method #4: Use the End of Month formula
This is useful when you need to set the date to the end of the current month. Or if you want to set a date at the end of the month and add a few months plus a few days.
As an example, your customer purchased something, and they need to make a payment at the end of next month. But you will give them 5 days’ grace before following up. So you need to get the end of next month’s date, and add 5 days to it.
Step #1: Open your MS Excel workbook
Open the Excel sheet in which you have the desired date field and wherein you want to add days.
Step #2: Select the cell
Click on the cell where you want to see the results of the formula.
In our example, it is B2 the cell next to A2.
Step #3: Enter the Excel formula
The Excel formula is =EOMONTH(Cell Number,y)+x
where Cell Number represents the cell with the date in;
x represents the additional days to be added after the end of the month, and
y represents the number of months you wish to add.
In our example, that would be =EOMONTH(A2,3)+1
Step #4: Press enter
Press enter to get the result for the first entry in the excel sheet.
Once done, click on the bottom right corner of the results (B2), where a little square will appear.
Hold the mouse button down, and drag the cursor down the remaining cells in Column B to calculate the other dates.
Conclusion
The above methods make your life easy and save you a lot of time. They also ensure that the output is error-free. Use any of the methods to add days to a date quickly and easily.