We need to do project scheduling and time management for various purposes. For that, we can use MS Excel for calculating dates. This article will show you how to subtract/Minus days from today’s date in Excel.
Download the Practice Workbook
4 Ways to Subtract/Minus Days from Today’s Date in Excel
1. Subtracting Days To Date With Formula
We can subtract dates from any dates using a simple subtraction formula in Excel. For this, let’s consider we have some dates, and we will figure out the dates before 20 days.
Step 1: Enter the formula<span style="white-space: pre-wrap;"> =B4+20 </span>
in cell C4.
Step 2: Copy down the formula up to C10.
2. Subtracting Days from Date Using DATE Function
The DATE function is another way to subtract a day, month, a year from any date. Let’s consider the same example above, but now we will subtract 20 days from the given date using the DATE function. The function syntax is like this:
<span style="font-size: 11.0pt; font-family: 'Arial',sans-serif; color: black;">= DATE(YEAR(date), MONTH(date), DAY(date)-(no. of days to be subtracted))</span>
For this method, we will consider the day parameter of the DATE function. Other parameters will be unchanged here.Â
Step 1: Enter the formula =DATE(YEAR(B4), MONTH(B4), DAY(B4)-20)
in cell C4 and press the Enter key.
Step 2: Copy down the formula up to C10.
3. Subtracting Days To Date Without Formula (Paste Special)
We will do the same task mentioned in method one but without a formula. We will use the Paste Special option where the procedure is not required.
Step 1: Right-click on cell D4 and select copy or use keyboard shortcut Ctrl+C.Â
Step 2: Select the date cells that you want to subtract and Right-click on them. Then select Paste Special.Â
Step 3: Then follow the steps:
- Select the Values option.
- Then select the Subtract option.
- Lastly, press the OK button.
Step 4: Then all the previous dates will be changed will change all the dates like this.
Now our dates are changed like this using the Paste Special option.
4. Using Function TODAY to Subtract/Minus Dates from Today’s DateÂ
Sometimes we may need to calculate the total days from any specific dates. This way, Excel helps us by providing another function that is TODAY. For example, let’s consider a dataset of some employee’s joining dates. Now we will calculate the total working dates up to today.
Step 1: Enter the formula <span style="white-space: pre-wrap;">=TODAY()-C4</span>
in cell D4.
Step 2: Copy down the formula up to D9.
Step 3: Now, convert the dates into days by following the process.Â
- Go to the Home tab.
- Then go to the Number section.
-
Select the Number option.
Step 4: Then, all the days from the joining dates will be calculated up to today.
➥ Related: Excel Formula to Calculate Number of Days Between Today & Another DateÂ
Downloadable Day Subtractor Calculator
For your practice, here is the day subtractor calculator. With this, you can calculate the remaining dates up to today. This workbook is fully customizable, and all the instructions are provided in this file for better understanding.
Conclusion
These are the ways to do day subtraction from any dates in Excel. Here I have discussed different ways with relevant examples step by step. I hope these methods will help you to resolve this type of issue. Please inform us if you’ve any difficulties using these methods.
Further Readings
- Excel Formula to Count Days from Date to Today (8 Effective Ways)
- Excel formula to Calculate Age on a Specific Date
- Number of Days Between Two Dates Calculator
- How to Calculate Overdue Days in Excel (using Functions)
- How to Add/Subtract Years to a Date in Excel
- How to calculate years of service in Excel (3 easy ways)
- How to calculate working days in Excel excluding weekends & holidays
- Calculate The Difference between Two Dates in Excel
hi, i need to substract dates formula of machine break down hours
i.e : machine breakdown started A1:1/1/2021 B1: 6:00 am
job done C1: 15/1/2021 D1: 4:00 PM
with minimum 10 hours each day = 150 hours(15 days)
can some one help me out on this formula..