Calculate the Number of Days Between Two Dates

Photo of author
Written by:

Mastering the technique to calculate the number of days between two dates in Excel is an indispensable skill, particularly for those new to Excel’s vast capabilities. This post not only guides you through the process of determining the number of days between a start and end date but also illuminates the underlying formula, making it a go-to resource for both novices and seasoned Excel users.

Introduction

As an Excel beginner, you’ll find that manipulating dates is a common task, whether you’re preparing reports or organizing data. A frequent need is to calculate the number of days between two dates – a seemingly straightforward task that can sometimes be tricky due to certain nuances in Excel’s formulae.

This guide is tailored to help you navigate these challenges with ease. You’ll learn not just the steps to perform this calculation, but also the logic behind it, empowering you to apply this knowledge in various contexts.

Example

In this example there are two dates, a ‘Start Date’ in cell A2 and an ‘End Date’ in Cell B2. The aim is to calculate the difference in days between those two dates.

Calculate the Number of days between two dates Excel

The Solution in Excel

In order to calculate the number of days between two dates in Excel it helps to understand the logic. To get a number of days the math dictates taking the latest date and subtracting the earliest date. For this example that means:

  • Difference in Days = (End Date – Start Date)

To achieve this in Excel the formula is to subtract the End Date (in this example found in cell B2) from the Start Date (found in cell A2). This is how it looks in Excel:

  • =B2 – A2

Calculate the Number of days between two dates Excel

Common Mistakes with Date Calculations

Output is a Date

The main nuance that Excel developers come across when calculating the number of days between two dates in Excel is that the output can sometimes show in a date format, like this:

Calculate the Number of days between two dates Excel

If that occurs it can be quickly fixed by changing the Format of the cell (C2 in this example) to a ‘numeric’ rather than a ‘Date’ format which it has currently taken on.

To do this Right-Click on the cell, or cells, that need Formatting. Next select ‘Format Cells‘ from the options box that shows up.

Calculate the Number of days between two dates Excel

When the Format Cells options window opens you can change the type to ‘Number‘ and click ‘OK’. That will format the cell to number and the result is as you would expect.

Calculate the Number of days between two dates Excel

Output is All Hashes

Another nuance is that it is easy to accidentally reverse the formula order and that will lead to a negative number being shown, or if the date format is being triggered you will see the cell filled with lots of hashes:

Calculate the Number of days between two dates Excel

There could be times where it is more appropriate to have a negative number, for example if you are counting down days to a deadline, but generally the formula will always take the format of:

  • Difference in Days = (Latest Date – Earliest Date)

If the result looks odd try reversing your calculation and if you see the hashes then you know you need to change the cells Format to Number as shown previously.

Conclusion

In conclusion, understanding how to calculate the number of days between two dates in Excel is a fundamental skill for efficient data management. This post has equipped you with the knowledge to perform this calculation using a simple subtraction formula, while also alerting you to common pitfalls such as incorrect format outputs. Whether you’re new to Excel or refining your skills, these insights will enhance your data analysis capabilities.

Keep Excelling,

Now that you’ve mastered calculating the number of days between two dates, why not expand your expertise? Discover how to calculate the number of working days between two dates in Excel, a vital skill for project planning and business analysis.

Our next post, How to Calculate the Number of Working Days Between Two Dates in Excel, covers this topic in-depth, including handling weekends and holidays. Click to learn how to make your date calculations even more precise and relevant to the working world.

With over 20 years in the field, I've become a go-to consultant for all things Excel. From basic formulas to complex macros and dashboards, I've tackled a wide range of Excel challenges. My goal? To demystify Excel's vast capabilities and empower you with practical skills and insights. Join me in this journey to unlock the full potential of Excel, one step at a time. I'm here to share the exploration of Excel's capabilities and help you learn a few things along the way!