Calculate Number of Days Between Two Dates in Excel to gain precise control over time-related data and project timelines. This functionality enables detailed and accurate planning, ensuring that every deadline is met and every schedule is maintained with the utmost precision. Whether it’s for personal planning, financial calculations, or professional project management, understanding how to effectively utilize this tool can transform your approach to time management and increase your efficiency. Embrace this essential skill to enhance your Excel proficiency and make the most of every day.
This Tutorial Covers:
- How many days between dates (without any function)
- Calculate the number of days between dates with DATEDIF Function
- Calculate the number of days between dates with DAYS Function
- How to calculate the number of days between today and another date
- How to calculate working days between two dates in Excel
- Explain detail of Excel NETWORKDAYS Function– Syntax & Arguments
- Explain detail of Excel NETWORKDAYS INTERNATIONAL Function – Syntax & Arguments
- How to calculate Weekends between two dates in Excel
1. How many days between dates (without any function)?
In Excel, subtracting one date from another is the simplest approach to figure out how many days are between two dates:
Newer date – Older date
For instance, you may use the following formula to determine how many days there are between dates in cells A2 and B2:
=B2 – A2
If A2 is a more recent date and B2 is a more distant one.
An integer representing the number of days between two dates is the outcome:
How this formula operates:
Microsoft Excel records date as serial numbers beginning with January 1, 1900, which is represented by the number 1, as you are probably aware. In this approach, the dates of January 2, 1900, January 3, 1900, and so on are all maintained as numbers. As a result, you truly subtract the integers that represent the dates when you subtract one from the other.
In our case, the formula in C3 takes the numeric values of 44527 (the numeric value of 27-Nov-21) and 44228 (the numeric value of 1-Feb-21) and subtracts them to get a result of 299 days.
The benefit of this approach is that it consistently yields accurate results regardless of which date is newer or older. The formula yields a difference as a negative integer when subtracting a later date from an earlier date, like in row 4 of the picture shown above.
2. Calculate number of days between dates with DATEDIF Function
The DATEDIF formula in Excel, which is specifically made to calculate the date difference in multiple units, including days, months, and years, is another way to count the number of days between dates in Excel.
You need to provide the start date, finish date, and “d” unit in the three arguments to calculate the number of days between two dates:
DATEDIF(start_date, end_date, “d”)
The formula looks like the following in our example:
=DATEDIF(A2, B2, “d”)
A DATEDIF formula, unlike the subtraction operation, may only subtract an older date from a newer date; it cannot do the opposite. The formula returns a #NUM! if the start date is later than the end date, at row 4 of the above screenshot, there is a mistake.
Note: DATEDIF is an undocumented function, which means that Excel’s list of functions does not include it. You will need to manually type each argument if you want to create a DATEDIF formula in your worksheet.
3. Calculate number of days between dates with DAYS Function
One other straightforward method for users of Excel 2013, Excel 2016, and Excel 2019 to determine the number of days between two dates is the DAYS function.
Please note that, in contrast to DATEDIF formula in Excel, a DAYS Formula in Excel requires the arguments to be presented in the opposite order:
DAYS(end_date, start_date)
The below is the structure that our formula takes:
=DAYS(B2, A2)
Similar to subtraction, depending on whether the end date is later than the start date or earlier, it returns the difference as a positive or negative number:
4. How to calculate number of days between today and another date?
Figuring out how many days are left till or before a specific day is a specific instance of “how many days between dates” math. You can use any of the calculations covered above for this by substituting the TODAY function for one of the dates.
To determine how many days have passed since a certain date, i.e., from that date to Today:
TODAY() – past_date
To determine how many days remain till a date, i.e., how many days remain until today:
Future_date – TODAY
Let’s use A2 as an example and figure out how much time has passed since today.
=TODAY() – A2
Let’s now calculate the number of days that distance today from a future date:
=A2-TODAY()
5. How to calculate working days between two dates in Excel?
Two functions in Excel can calculate the total number of working days between two dates while also taking into account weekends and designated holidays.
- Use the Excel NETWORKDAYS function when Saturday and Sunday are the weekend days.
- Use the Excel NETWORKDAYS INTERNATIONAL function when Saturday and Sunday are not the weekend days.
- Explain detail of Excel NETWORKDAYS Function– Syntax & Arguments:
Let’s first quickly review the syntax and arguments for the NETWORKDAYS Formula in Excel.
Syntax:
=NETWORKDAYS(start_date, end_date, [holidays])
Arguments:
- start_date- a date value that denotes the beginning date.
- end_date- a date value that denotes the expiration date.
- [Holidays]-(Optional) It is a selection of dates that are not included in the computation. These might be statutory holidays, for instance. It is possible to enter this as a reference to a named range, an array of serial numbers, or a range of cells that include the dates.
Let’s start with an example where you need to determine how many working days (also known as business days) there are between two dates using weekends of Saturday and Sunday.
Use the formula below in C2 and duplicate it in all cells to determine the number of working days (Column C) when the start date, finish date, and holidays are specified:
=NETWORKDAYS(A2,B2,$E$2:$E$9)
- Explain detail of Excel NETWORKDAYS INTERNATIONAL Function – Syntax & Arguments:
When the weekends fall on days other than Saturday and Sunday, this NETWORKDAYS function doesn’t always perform as well.
For instance, the weekend is Friday and Saturday in middle-eastern nations, or a six-day workweek might be the norm in other occupations.
The NETWORKDAYS.INTL function in Excel can be used to handle situations like this.
Let’s quickly review the arguments and syntax of the Excel NETWORKDAYS.INTL function.
Syntax:
=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])
Arguments:
- start date is a date value that denotes the beginning date.
- end date is a date value that denotes the expiration date.
- [weekend] – (Optional) You can specify the weekend here, which can be any combination of two or just one day. In the absence of this, the weekend is considered to be Saturday and Sunday.
- [Holidays]-(Optional) It is a time frame that is left out of the calculations. These might be statutory holidays, for instance. This might be written as a reference to a set of cells containing the dates or as an array of serial numbers that stand in for the dates.
Let’s look at an example of how to determine how many working days there are between two dates where the weekends are Friday and Saturday.
Assume you have the dataset as follows:
Using Friday and Saturday as the weekend, use the following formula to get the number of working days (Column C):
=NETWORKDAYS.INTL(A2,B2,7,$E$2:$E$9)
The formula is instructed to treat Friday and Saturday as the weekend by the third parameter in this expression (the number 7).
6. How to calculate Weekends between two dates in Excel?
The NETWORKDAYS function counts working days, but we can also use it to determine the number of weekends there are in between two dates.
Let’s say we have the dataset as displayed below:
You may calculate how many weekends there were between two dates using the following formula:
=DAYS(B2,A2)+1-NETWORKDAYS(A2,B2)
Application of Calculate Number of Days Between Two Dates in Excel
- Project Management: Calculate the number of days between start and end dates to plan and track project timelines, ensuring deadlines are met and resources are allocated efficiently.
- Age Calculation: Determine the exact age by calculating the number of days between a person’s birthdate and the current date, useful for age-specific analyses or verifications.
- Contract Duration: Measure the duration of contracts or agreements by calculating the days between the start and end dates, aiding in legal and financial planning.
- Inventory Turnover: Assess inventory turnover rates by calculating the number of days between inventory acquisition and sale, helping to optimize stock levels and reduce holding costs.
- Interest Calculation: Determine the period for interest calculations on loans or investments by finding the number of days between the relevant dates, impacting financial decisions and analyses.
- Event Planning: Plan and organize events effectively by calculating the time span between the planning start date and the event date, ensuring all tasks are completed on time.
You may be interested:
