There are many ways to calculate the number of days between two dates in Excel. 1. The basic method is to subtract the start date by using the end date, such as =B1 - A1; 2. If you only count the working day, use the NETWORKDAYS function and optionally exclude holidays, such as =NETWORKDAYS(A1, B1, D1:D10); 3. You can add or subtract 1 to control whether the start and end dates are included; 4. Use the DATEDIF function to calculate the complex differences between years, months and days, such as =DATEDIF(A1, B1, "d").

Calculating the number of days between two dates is actually quite simple in Excel, the key is to use the right method. Many people will directly subtract from each other, but there are some details that are easy to ignore, especially when you need to consider holidays, weekdays, etc.

1. Basic method: Directly subtract two dates

If you just want to calculate how many days there is between the two dates, just use "end date-start date".
For example, A1 is the start date and B1 is the end date. Then enter =B1 - A1 in C1 to get the difference in the number of days.
Note: If the result is a negative number, it means that the end date is earlier than the start date. At this time, you can add an ABS() function to get the absolute value.

2. Only counting working days: Use the NETWORKDAYS function

Sometimes you only care about working days, such as scheduling project cycles and taking leave days. You can use the NETWORKDAYS function at this time.
The basic writing method is:

 =NETWORKDAYS (start date, end date, [holiday])

• The first two parameters are required, and the third is an optional holiday list.
• If you have holiday arrangements, such as national statutory holidays, you can list them in a region, such as D1:D10, and then pass them in as the third parameter.

For example:

 =NETWORKDAYS(A1, B1, D1:D10)

This will eliminate weekends and holidays you designated, which are only real working days.

3. Tips for including or excluding specific dates

Sometimes you might want to count "including the start date but not the end date" or the other way around. At this time, you can add or subtract 1 to the result.
For example, if you want to calculate that from today to tomorrow is two days (including the beginning and the end), then =B1 - A1 1 is fine.
If you are counting the "how many nights to stay", you usually only count the number of days in the middle, and you don't need to add 1.

4. Use the DATEDIF function to calculate more complex time difference (year, month, and day)

Although Excel does not list DATEDIF in common functions, it is actually very practical. It can count how many years, months, and days the difference between two dates.
The basic writing method is:

 =DATEDIF (start date, end date, "unit")

Commonly used units are:

• "d" : Total days
• "m" : the number of whole months
• "y" : the number of years
• "ym" : Remove the remaining months of the whole year
• "md" : Remove the remaining days of the whole month

for example:

 =DATEDIF(A1, B1, "d") // Total days=DATEDIF(A1, B1, "ym") // The remaining months

Basically these are the methods, just choose the right one according to your needs. Just like the average day difference is enough, it is enough to directly subtract the difference between the number of days, but if it involves working days or more complex year and month, it will be more accurate to use NETWORKDAYS or DATEDIF.

The above is the detailed content of How to calculate days between two dates in Excel. For more information, please follow other related articles on the PHP Chinese website!