Excel is a powerful software that can do more advanced tasks than you may imagine. You can get Excel to automatically update the date in your Excel document each time you open it. Learning how to subtract a date from today in Excel can help you find the number of days between a given date and today’s date.
When you want to know the number of days between two dates whether the date is in the future or the past, you can use Excel to calculate it. You can easily use Excel formulas to tell you the number of days. This can be useful if you want to calculate the number of days you have for vacation or how long you have until you need to pay for an item.
How to subtract a date from today in Excel
To subtract dates from today if the dates are in the past or the future, follow these steps:
- Open and prepare Excel
- Dates are in the future
- Dates are in the past
1] Open and prepare Excel
The first step in the process is to open and prepare Excel. Go to start then type Excel, and click the Excel icon to open it. When Excel is opened, create a new document or open an existing document that you want to work on.
2] Dates are in the future
Here you will see how to subtract from today if the date or dates you want to use are in the future. In this article, additional information will be added to Excel to give you an idea of how you can use the formula in a real-world situation.
The Excel formula you would use would be formulated as =cell number with date – today (). You will plug in your cell references that match your information. The Excel document created as an example will be used to show how to do this. You will need to format your data to suit your needs.
This is the Excel document with the data added.
You will notice that the dates are written out, you can use whatever format you want for your dates. To format the dates, select the cells containing them and right-click. When the menu appears choose Format cells.
When the Format cells menu appears, click Date. You would then scroll down in the date section and choose the date format that you prefer. When you have chosen click Ok to accept and confirm.
With the date written in and formatted, you can now enter the formula to subtract the date. You would enter that in the appropriate cell for your Excel document. In this article, it will be entered in the first cell under DAYS TO BDAY.
This is the formula that would correspond with the Excel document used as a demonstration
=D6-TODAY()
D6 is the cell where you wrote the date, the minus sign (-) before today tells Excel to subtract the date in the future (D6) from today’s date.
To ensure that your answer is a number with no decimal points, you can format and use Number and set Decimal places to 0.
When you have finished with the first one, you can click and drag to duplicate the same formula in all the cells below.
TIP: If you intend to add more dates below, you can drag the formula cell selection below where you have the date. This will allow the calculations to be done automatically whenever you enter dates.
This is the Excel document completed. Note that the formula did not depend on the date that was entered above in the Today’s date. That date is just a visual reference for the user.
You can make today’s date change each new day. To do that, go to cell F2 (put your cell reference)and write the following formula.
=TODAY()
You can then choose the date format that you want for the date.
This is the Excel document formatted.
3] Dates are in the past
Here you will see how to subtract from today if the date or dates you want to use are in the past. In this article, additional information will be added to Excel to give you an idea of how you can use the formula in a real-world situation. When the dates are in the past, you typically want to know how many days have passed after a particular event. To do this the formula is similar to the one for when dates are in the future, it is just tweaked a bit to show the difference. You are subtracting the date in the past from today’s date.
Below is the formula that you would use to calculate.
= TODAY() -D6
When you have entered the data and all cells are populated as you need, you will realize that the numbers are negative. You will need to remove the negative signs automatically. To make numbers show up without the negative or any other signs, you would use Excel’s ABS function. The ABS function will return the Absolute value of the number. You can use the ABS on a number or a calculation and the result will come out without any negative signs. In this case, the ABS function will be used on a calculation and it will be written as follows.
=ABS(D6-TODAY())
This formula tells Excel to return the absolute value of the result of the calculation. In the case of the number of days, you do not need any negative numbers so this would be ok.
This is the completed table with the numbers positive.
Being able to subtract a date from today is a great way to accurately calculate the number of days between dates.
Read: How to stop Excel from rounding numbers
What does the ABS function in Excel do?
The ABS function or Absolute value is the positive value of the number, for example, the absolute value of -111 is 111. ABS would remove the negative sign and leave you with the absolute number. You can choose to get the absolute value of a number or the absolute value of the results of a formula.
=ABS(-1110 would be 111
=ABS(D6-D4) would give the value of D6-D4 as a number without any negative signs.
How can I add or subtract months from a date using the EDATE function in Excel?
To use the EDATE function you will need the starting date and the number of months to add or subtract. To add you put the numbers as they are and to subtract place a negative sign (-) in front of the number. You would place the start date in one cell and the number of months to add or subtract in another cell.
If you have the year in cell A5 and the months to add in cell B5 your formula would look like this: =EDATE (A5,B5).
