How to Calculate Day Difference Between Two Dates in Excel

How to Change Date Format in Excel

Before you start calculating the difference in days between two dates in Excel, you may want to change the date format in Excel to suit your preferences. Excel has many different date formats you can choose from, such as:

• Standard date formats, such as 31/01/2024 or 01/31/2024.
• Custom date formats, such as 31-Jan-2024 or January 31, 2024.
• International date formats, such as 2024-01-31 or 2024/01/31.

To change the date format in Excel, you can follow these steps:

1. Select the cell or range of cells that contains the dates that you want to format.
2. Click the Home tab in the ribbon, and then click the Number Format icon in the Number group.
3. Select one of the available date formats from the drop-down list, or click More Number Formats to see more options.
4. If you select More Number Formats, select a Date category from the Format Cells dialog box, and then select one of the available date formats from the list, or create your custom date format by using date format codes.
5. Click OK to apply the date format you selected.

Here are examples of some different date formats you can use in Excel:

Well, I’ll continue writing the truncated article. Here is the next section of the article:

How to Calculate the Day Difference Between Two Dates in Different Ways

After you learn the functions of DATE and DATEDIF, you may want to know how to calculate the difference in days between two dates in various ways, such as calculating calendar days, weekdays, or weekdays with holidays. Here are some ways you can use it:

How to Calculate Calendar Days Between Two Dates

A calendar day is a day that belongs to a regular calendar, regardless of whether it is a working day or a holiday. To calculate calendar days between two dates, you can use one of the following methods:

• Use DATEDIF functions with a value of D for the unit argument. For example, if you want to calculate how many calendar days between January 1, 2024, and January 31, 2024, you can use the following formula:

=DATEDIF(“01/01/2024”, “31/01/2024”, “D”)

The result is:

30

• Use the subtraction operator (-) to subtract the start date from the end date. For example, if you want to calculate how many calendar days between January 1, 2024, and January 31, 2024, you can use the following formula:

=“31/01/2024” - “01/01/2024”

The result is:

30

• Use a NETWORKDAYS function with a value of 7 for the weekend argument. The NETWORKDAYS function is a function that calculates the number of working days between two dates, taking into account holidays and weekends. If you specify a value of 7 for the weekend argument, then the NETWORKDAYS function considers all days as working days, thus calculating calendar days. For example, if you want to calculate how many calendar days between January 1, 2024, and January 31, 2024, you can use the following formula:

=NETWORKDAYS(“01/01/2024”, “31/01/2024”,, 7)

The result is:

30

How to Calculate Working Days Between Two Dates

A working day is a day that is not a weekend or holiday. By default, Excel considers Saturday and Sunday as weekends, but you can adjust what days are considered weekends by using the weekend argument in the NETWORKDAYS function. To calculate the working days between two dates, you can use one of the following methods:

• Use NETWORKDAYS functions without the weekend argument or with a value of 1 for the weekend argument. The NETWORKDAYS function will count the number of working days between two dates, assuming Saturday and Sunday as weekends. For example, if you want to calculate how many working days between January 1, 2024, and January 31, 2024, you can use the following formula:

=NETWORKDAYS(“01/01/2024”, “31/01/2024”)

Or:

=NETWORKDAYS(“01/01/2024”, “31/01/2024”,, 1)

The result is:

22

• Use the NETWORKDAYS function with other values for weekend arguments. The NETWORKDAYS function will count the number of working days between two dates, assuming the day you specified is a weekend. You can specify a value between 1 and 7 for the weekend argument, which represents the first day of the weekend. For example, if you want to calculate how many working days between January 1, 2024, and January 31, 2024, assuming Friday and Saturday as weekends, you can use the following formula:

=NETWORKDAYS(“01/01/2024”, “31/01/2024”,, 6)

The result is:

20

• Use a NETWORKDAYS function with array values for the weekend argument. The NETWORKDAYS function will count the number of working days between two dates, assuming the day you specified is a weekend. You can specify an array value that contains numbers between 1 and 7 for the weekend argument, which represents the day you want to think of as a weekend. For example, if you want to calculate how many working days between January 1, 2024, and January 31, 2024, by considering Monday, Wednesday, and Friday as weekends, you can use the following formula:

=NETWORKDAYS(“01/01/2024”, “31/01/2024”,, {2, 4, 6})

The result is:

12