If you’ve used Microsoft Excel before, it’s likely that you’ve had to work with a spreadsheet where dates were manually entered - but did you know that Excel includes an array of functions to make it easy to calculate dates? It’s true!

Today, I’m going to cover some of the more common ways you can manipulate dates in Excel, but I’ll also link to some external resources in case you’d like to really dive deep into the date functions available.

Note This tutorial uses dates in the American MM/DD/YYYY format (e.g. 08/29/2024 for 29th August 2024). If you’d like to know how to set Excel to use the British date format, please see this helpful article on the University of Southampton website.

About Date Calculations in Excel

The nice thing about calculating dates with Excel is that it’s really quite intuitive. You can easily subtract one date from another, and you can also take a specific number and then add that to - or substract it from - another date. You can also factor these functions into more complex formulae, as I’ll demonstrate later on.

If working out dates manually takes up any significant amount of time when updating your spreadsheets, you’ll do yourself a massive favor to learn to use some of the tools outlined in this article.

So, without any further adue, let’s get to it!

Our First Date Calculation in Excel

We’ll start off by working out the difference between two dates. Excel will return the number of days between the start and end date; all we have to do is write a basic subtraction formula to subtract the start date from the end date.

As you can see from the above screenshot, we’ve got a start date in Cell A2, and an end date in cell B2. To work out the days between the two dates, I’m going to type my formula into cell C2:

=(B2-A2)

A Quick Note About Number Formats!

Note You can skip this section if your formula worked as expected. If not, though, there’s a good chance that you just need to check the number formats you’re using.

When I first performed this calculation, I received the following result:

Excel provided my answer in date format, when what I needed was an answer in number format. It’s important to always ensure that you’re using the correct number format for your cells in Excel, whether that be for integers (regular numbers), dates, or currency.

To change the format of cell C2 to a number, what I’m going to do is right-click the cell, then click ‘Format Cells’:

Then, in the pop-up menu, I’ll change the number format to ‘Number’:

Note: These screenshots are from Office365, so things might look a bit different if you’re using the desktop version of Excel. The first which opens will usually be “Number”, and you should be able to amend the format from there.

I’ll also change the “Decimal Places” option to 0:

The Result

At this point, you should have the answer of your date subtraction calculation in a nice, readable integer format. Here’s the answer to the formula I typed above:

Pretty spiffy, eh? When working out the number of days between two dates, we always subtract the earlier date from the later date. Don’t worry if this seems a bit abstract at first – it’s just the way computers work, and you’ll get used to it!

Adding a Number to a Date

What if, instead of working out how many days are between two dates, I wanted Excel to tell me the date a certain number of days from now? Let’s say, 45 days from 29th August 2024?

All I’ll do is go to Cell C2 and enter the following formula:

=A2+B2

Which gives me:

Again, I had to make sure my cells were using the correct format for this calculation to give me a usable result:

• Cells A2 and C2 are both in date format
• Cell B2 is in a number format with zero decimal places.

Subtracting a Number from a Date

You can just as easily subtract dates in the same way:

In this situation, all I’d need to do is go to Cell C2 and type:

=A2-B2

And then I’d get:

The TODAY() Function

How about if I wanted to find out how many days it is until Christmas? Easy! I can just write:

=12/25/2024-(TODAY())

NOTE: The TODAY() function automatically uses the current date, which can be a handy little trick. I could just as easily work out what the date will be 25 days from now by typing:

=(TODAY())+25

Learning to use TODAY() will rarely make a huge difference, but it doesn’t hurt to know about it, especially for spreadsheets that require regular calculations based on the current date.

More Complex Date Calculations

Now, let’s move on to some slightly more complex examples of how to use date calculations in Excel.

In this next example, we’re going to work out car hire charges using a date calculation.

Here’s the scenario: I need to work out how much it will cost to a hire a car until 15th September. I’ll be picking the car up on 25th August. The price per day to hire the car is $54.95. Here’s the information in my spreadsheet:

We could just work out the number of days manually, and then multiply that by the price per day. Or, we could avoid the manual work altogether and just type:

=(B2-A2)*C2

See what we did there? As before, we subtract the earlier date from the later date, which will provide the number of days between the two dates to Excel. Then, before doing anything else, we ask Excel to multiply that result by the daily hire price of the car.

When I clicked enter after typing this formula, the result was provided as another nonsense date (“02/26/1903”). Once I changed the format of the cell to currency, though, I had my answer:

Using the NETWORKDAYS.INTL() function

Another neat future of Excel is its ability to tell you the net number of working days between two dates. This can be a very useful function in its own right.

However, it’s worth noting that the syntax for NETWORKDAYS.INTL can seem a bit awkward at first. If you’re just getting used to dates, I really wouldn’t worry too much about learning this right now; while it’s certainly a nice option to use sometimes, it’s in no way essential!

The format of the NETWORKDAYS.INTL() function is as follows:

=NETWORKDAYS.INTL(START DATE, END DATE, WEEKEND DAYS, HOLIDAYS)

Let’s take a look at each component of the formula:

• =NETWORKDAYS.INTL() tells Excel we’d like to work out the net number of working days between two dates.
• START DATE is, of course, the date we’d like to begin calculating from. This will be entered in YYYY,MM,DD format (e.g. 2024,08,29 for 29th August, 2024).
• END DATE is (you guessed it!) the date we wish to calculate to. This should also be in the same YYYY,MM,DD format.
• WEEKEND DAYS can be omitted if you’re using a standard Saturday/Sunday weekend. The only reason you’d need to enter anything here is if you were using a non-standard weekend. You can find a full list of the options available for this feature here.
• HOLIDAYS is the number of holidays that should be taken into account between the two dates. This number will not be excluded from the end result; it’s best to only use this option for paid, national, and state holidays.

Note: If you’re using a standard Saturday/Sunday weekend, but would like to include the number of holidays, then you will be required to use the WEEKEND DAYS option.

For example, if you were calculating the working days in 2025 for a standard Monday-Friday working week - and you wanted to factor in 25 days paid holiday - you would type this as:

=NETWORKDAYS.INTL(DATE(2025,01,01),DATE(2025,12,31),1,25)

If we didn’t enter a “1” for WEEKEND DAYS, Excel would think that the “25” referred to WEEKEND DAYS rather than HOLIDAYS.

Using the above information to guide us, we can work out the number of net working days between 1st September 2024 and 24th December 2024 by typing:

=NETWORKDAYS.INTL(DATE(2024,09,01),DATE(2024,12,12))

Which will provide the below result:

Note: NETWORKDAYS.INTL always returns a result which is inclusive of the end date used in the calculation. So, for example, if you use the formula to calculate the number of days until a date which is a Monday, the Monday will be included in the calculation. If you didn’t want this, you could just use the preceding Sunday as the end date in your calculation.

If you’re using a non-standard weekend – say, to calculate the number of working days where a Tuesday and Wednesday are the days off – you can just use the information on Microsoft’s page to work this out. Looking at that page, I can see that a Tuesday/Wednesday weekend are associated with the number “4”.

So the above example with a Tuesday/Wednesday weekend would just be typed as:

=NETWORKDAYS.INTL(DATE(2024,9,1),DATE(2024,12,12),4)

Which gives the below result:

In some situations, changing the weekend days won’t affect the end result; but it can be very useful for situations where accuracy is critical.

We can also add holidays to the above formula by adding them in as a fourth argument. Let’s say we want to include 8 holiday days. We can just type:

=NETWORKDAYS.INTL(DATE(2024,9,1),DATE(2024,12,12),4,8)

And now we have an updated answer:

In fact, we can even use this to calculate what the pre-tax earnings would be for an employee working this period. All we’d need to do is work out what their daily rate would be and factor that into the formula.

Note: You could also use a formula to work out the daily rate, but in our example we’ll just use a random figure.

If the employee was earning $90 per day, we could just type:

=(NETWORKDAYS.INTL(DATE(2024,9,1),DATE(2024,12,12),4,8))*90

And, after formatting the result as currency, I have my answer:

Note: As you can see, I’ve started adding more brackets to enclose separate calculations. Although not always necessary, it can make your formulas easier to read when they start to get a bit longer!

Brackets can also help with avoiding errors due to something called operator precedence. If you’re curious to learn more about brackets in formulas, check out w3schools’ page on Excel Parentheses.

Summary

Phew! This article was quite a slog, so well done if you’ve made it this far.

Working with dates in Excel can be quite an arduous task at times, so it’s nice to start learning about some of the formulas and functions we can use to make things easier for ourselves. Don’t worry if any of the more complex parts of this article are overwhelming - most of us start by mastering the basics, and then learning the fancier tools as and when needed.

Hopefully this article has helped you out, and maybe even taught you some tricks which will help you speed up your work processes. If you’re really keen to see the full extent of date and time functions available in Excel, this Microsoft page is a good place to start!