How to Calculate Day Difference Between Two Dates in Excel

How to Calculate Working Days with Holidays between Two Dates

A working day with a holiday is a working day that is also a holiday. You may want to calculate workdays with holidays between two dates if you want to find out how many workdays you missed due to holidays, or how many workdays you have to complete your project by accounting for holidays. To calculate weekdays with holidays between two dates, you can use one of the following methods:

• Use the NETWORKDAYS function with the holidays argument. The NETWORKDAYS function will count the number of working days between two dates, subtracting the holidays you specify. You can specify holidays arguments as cell references, cell names, or arrays that contain the holiday dates that you want to assume. For example, if you want to calculate how many working days with holidays between January 1, 2024, and January 31, 2024, assuming January 1, January 17, and January 25 as holidays, you can use the following formula:

=NETWORKDAYS(“01/01/2024”, “31/01/2024”, {“01/01/2024”, “17/01/2024”, “25/01/2024”})

The result is:

19

• Use WORKDAY functions with holiday arguments. The WORKDAY function is a function that calculates a working date that falls a certain number of working days before or after a certain date, taking into account holidays and weekends. If you use the WORKDAY function with the holidays argument, you can calculate weekdays with holidays between two dates in the following ways:
  • Specify the start and end dates as the start_date and end_date arguments.
  • Specify the number of working days between the start and end dates as the days argument.
  • Specify the holiday you want to consider as a holidays argument.
  • Subtract the result of the WORKDAY function from the end date.

For example, if you want to calculate how many working days with holidays between January 1, 2024, and January 31, 2024, assuming January 1, January 17, and January 25 as holidays, you can use the following formula:

=“31/01/2024” - WORKDAY(“01/01/2024”, 22, {“01/01/2024”, “17/01/2024”, “25/01/2024”})

The result is:

3

How to Use Dynamic Formulas and Tables to Automatically Calculate the Day Difference between Two Dates

If you want to calculate the difference in days between two dates automatically, without having to enter a formula every time you change the date, you can use formulas and dynamic tables. Dynamic tables are a feature of Excel that allows you to analyze, filter, and organize data easily and quickly. You can create a dynamic table from the data you have, and use formulas to calculate the difference in days between two dates in a dynamic table. Here are the steps to do so:

1. Create your data in tabular form, with columns for start date, end date, and day difference.

2. Select the entire table, then click the Insert tab in the ribbon, and click the Table icon in the Tables group.
3. Check the box My table has headers, then click OK. You’ll notice that your table now has special formatting and features, such as filters, sorting, and automatic naming.
4. Click any cell in the table, then click the Design tab in the ribbon, and rename your table to your liking in the Table Name box. For example, you can name your table as TabelTanggal.
5. Click the first cell in the Day Difference column, and then enter a formula to calculate the difference in days between two dates. You can use any of the formulas you’ve learned before, but make sure you’re using dynamic table references, not static cell references. A dynamic table reference is a way to refer to a cell or range of cells in a table by using table names and column names instead of cell addresses. For example, you can use the following formula:

=DATEDIF([Start Date], [End Date], “D”)

Where:

• [Start Date] is a dynamic table reference for cells in column Start Date in the same row as the active cell.
• [End Date] is a dynamic table reference for cells in column Tanggal Akhir in the same row as the active cell.

6. Press Enter to enter a formula, and you’ll see that it will be applied to the entire column Day Difference automatically, thanks to the AutoFill feature of the dynamic table. You’ll also notice that the formula adjusts automatically if you add or remove rows in the table. You can change the dates in the Start Date or End Date column, and the formula in the Day Difference column will automatically recalculate the difference in days between the two dates. Here’s an example of the final result of your dynamic table:

By using dynamic tables and formulas, you can calculate the difference in days between two dates automatically, and make your tables more flexible and dynamic.