How to Subtract a Date from Today in Excel

Have you ever found yourself in a situation where you're juggling project deadlines, keeping tabs on due dates, or just curious about the time between two important events? I think most of us have. Learning how to subtract a date from today in Excel can be your secret weapon for effortlessly managing dates and staying organized. Whether you're a Microsoft Excel novice or a seasoned pro, this guide is here to make date subtraction a piece of cake. Let's dive in!

How to Determine Today's Date

Before we delve into the nitty-gritty details, let's start with the fundamentals of subtracting a date from today in Excel. This will serve as our foundation for exploring more advanced techniques later on.

The TODAY Function

The TODAY function is your go-to tool for obtaining the current date in Excel. It's a dynamic function, meaning it updates automatically whenever you open your workbook. To use it, simply enter “=TODAY()” in a blank cell, and voilà, you have today's date!

The great thing about this function is that you can use a simple formula to determine dates like yesterday and tomorrow, by using the following method:

Yesterday: =TODAY() – 1

Tomorrow: =TODAY() + 1

Now, let's move on to the heart of the matter: subtracting a given date from today.

How to Subtract a Date from Today in Excel

One of the most straightforward methods to subtract a date from today is by using a simple subtraction formula, just like we did to find yesterday's date. This formula involves subtracting the desired date from the current date (obtained using the TODAY function). Here's how it's done:

Enter Your Start Date

In your Excel sheet, select a cell where you want the result to appear. This will be your output cell. In an adjacent cell, enter the date you want to subtract from today. This is your starting date. Your end date is today. Let's choose C4 to be our output cell and A4 to be our starting date (we'll say January 1, 2021) we want to subtract from yesterday.

Apply the Formula

In your output cell, enter the following formula:

=TODAY() – B4 [Cell Reference of the Start Date]

I should point out here that I had to format the output cell because when I originally used the formula, Excel assumed I wanted the result in date format since my above formula deals with dates, but actually I want a numeric value indicating the number of days. The result originally looked like this:

You can change the format of a cell by using the pulldown menu from the Home Ribbon, Number section. You would choose “Number” in this instance like in the following example.

Notice also how the “Number” selection notes that it's 975.00 with two decimal places, you will need to format decimals to zero decimal places because we only want the result as a whole number to show full days. If you're uncertain how to do this, check out my How to Insert Pi in Excel, I explain this process with images.

There is another easy way that you can subtract a date from today in Excel. It's almost the same as above except you would have “today” entered in its own cell value and it would act as a cell reference for your ending date. Let's assume that's entered into A4 Then the formal would look like this:

=A4 – B4

If at any point in these calculations, you end up with a negative number (negative value), it simply means that you have the formula backwards. So in the above example, if your formula looked like this:

=B4 – A4

Your formula result would be a negative number or value instead of a positive number (positive value).

Different Ways to Subtract Dates in Excel

Now that you've mastered the simple formula, let's explore some other methods for subtracting dates in Excel. Each approach has its unique advantages and use cases.

The DATEDIF Function

The DATEDIF function (short for Date Difference) is a hidden gem in Excel. It calculates the difference between two dates in various units, such as the total number of days, number of months, or number of years. I describe this function in great detail in my blog post How to Subtract Dates in Excel to Get Days, Months, or Years. I won't go into as great of detail here, but to use DATEDIF:

1. In your output cell, enter the following formula
   
   =DATEDIF(start_date, TODAY(), “d”)
   
   Using our example above, you can replace [Start Date] with the cell reference of your starting date (B4), and TODAY() with cell A4.
   
   =DATEDIF(B4, A4,”d”)
2. Excel will return the difference in days between your start date and today.

Using the DATEDIF function you can request the output to be in months or years as well.

The DAYS Function

The DAYS function calculates the number of days between two dates. Here's how to use it:

1. In your output cell, enter a formula like this to find the days between today and your start date:
   
   =DAYS(TODAY(), start_date)
   
   Replace the [start date] with the cell references of your starting date and also for “today” as we have been above. Then change “TODAY()” in the formula to the cell reference (in our case “A4”).
2. Excel will return the number of days as the result.

The WORKDAY Function

The WORKDAY function allows you to calculate a future date based on a specified number of workdays (business days) from today. This can be incredibly useful for tracking due dates in projects or tasks.

1. In your output cell, enter the following formula:
   
   =WORKDAY(TODAY(), days, [Holidays])
   
   Replace [days] with the desired number of business days you want to add to today's date.
   
   To add [Holidays], you would have a list of the holiday dates or date serial numbers that you would reference, but we'll talk more about this in How to Convert Serial Numbers to Dates in Excel. So for now we'll just use zero for no holidays.
   
   Excel will provide you with the due date, factoring in weekends and any holidays you specify.

The NETWORKDAY Function

On the flip side of the WORKDAY Function, the NETWORKDAY function can help you determine how many working days you have left between today and a future date. This function will remove the weekend days and holidays you specify.

1. In your output cell, enter the following formula:
   
   =NETWORKDAY(TODAY(), end_date, [Holidays])
   
   Replace [end date] with the last working day. You will be calculating the number of working days between today and your end date.
   
   We will assume [Number of holidays] is zero again.
2. Excel will provide you with the due date, factoring in weekends and any holidays you specify.

Excel Date Subtraction Made Easy

In this guide, we've covered a number of ways to subtract a specific date from today in Excel. Whether you prefer a simple formula or want to explore the versatility of functions like DATEDIF, DAYS, WORKDAY, and NETWORKDAYS Excel offers a multitude of options to suit your needs.

So, the next time you find yourself needing to calculate the number of days, months, or even workdays between today and another date in your Excel sheet, remember the techniques discussed here. With a little practice, you'll become an Excel date subtraction expert, effortlessly managing dates and deadlines in your projects.