How to Calculate Day Difference Between Two Dates in Excel

Have you ever wanted to find out how many days you have left until your vacation? Or how many days have passed since you were born? Or how many workdays do you need to complete your project? If your answer is yes, then this article is for you.

In this article, you will learn how to calculate the day difference between two dates in Excel easily and accurately. You will learn about:

• DATE and DATEDIF functions that you can use to create and compare dates in Excel.
• How to change the date format in Excel to suit your preferences.
• How to calculate the difference in days between two dates in various ways, such as calculating calendar days, weekdays, or weekdays with holidays.
• How to use dynamic formulas and tables to automatically calculate the difference in days between two dates.

Let’s get started!

What is the DATE and DATEDIF function in Excel?

The DATE and DATEDIF functions are two very useful functions for working with dates in Excel. Let’s see what they do and how to use them.

DATE function

The DATE function is a function that allows you to create dates by specifying the year, month, and day separately. The syntax of the DATE function is as follows:

=DATE(year, month, day)

Where:

• year is the year you want to make, between 1900 and 9999.
• month is the month you want to create, between 1 and 12.
• day is the day you want to make, between 1 and 31.

For example, if you want to create a date of January 31, 2024, you can use the following formula:

=DATE(2024, 1, 31)

The result is:

31/01/2024

You can use the DATE function to create dates in a variety of ways, such as:

• Add or subtract years, months, or days from a specific date. For example, if you want to find out the date of six months after January 31, 2024, you can use the following formula:

=DATE(2024, 1, 31) + 6 30

The result is:

31/07/2024

• Use a cell reference or cell name as an argument to a DATE function. For example, if you have years, months, and days in cells A1, B1, and C1, you can use the following formula to create a date:

=DATE(A1, B1, C1)

The result is:

31/01/2024

• Use another function, such as TODAY, NOW, or YEAR, as the DATE function argument. For example, if you want to create today’s date, you can use the following formula:

=DATE(YEAR(TODAY()), MONTH(TODAY())), DAY(TODAY()))

The result is:

31/01/2024

DATEDIF function

The DATEDIF function is a function that allows you to calculate the difference between two dates in a specific unit, such as a year, month, or day. The syntax of the DATEDIF function is as follows:

=DATEDIF(start_date, end_date, units)

Where:

• start_date is the start date you want to compare, in a valid Excel date format.
• end_date is the end date you want to compare, in a valid Excel date format.
• unit is the unit that you want to use to calculate the difference, which can be one of the following values:

For example, if you want to count how many years have passed since you were born, and you were born on January 1, 2000, you can use the following formula:

=DATEDIF(“01/01/2000”, TODAY(), “Y”)

The result is:

24

You can use the DATEDIF function to calculate the difference between two dates in various ways, such as:

• Use a date created with the DATE function as an argument to the DATEDIF function. For example, if you want to calculate how many days are left until your vacation, and your vacation starts on July 1, 2024, you can use the following formula:

=DATEDIF(TODAY(), DATE(2024, 7, 1), “D”)

The result is:

152

• Use a cell reference or cell name as an argument to the DATEDIF function. For example, if you have start and end dates in cells A2 and B2, you can use the following formula to calculate how many months elapse between two dates:

=DATEDIF(A2, B2, “M”)

The result is:

6

• Using different values for argument units to calculate the difference between two dates in different units. For example, if you want to calculate how many years, months, and days elapsed between two dates, you can use the following formula:

=DATEDIF(A2, B2, “Y”) & “ tahun, “ & DATEDIF(A2, B2, “YM”) & “ bulan, “ DATEDIF(A2, B2, “MD”) &”hari”

The result is:

0 tahun, 6 bulan, 0 hari

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

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.

Conclusion

In this article, you have learned how to calculate the day difference between two dates in Excel easily and accurately. You’ve learned about:

• DATE and DATEDIF functions that you can use to create and compare dates in Excel.
• How to change the date format in Excel to suit your preferences.
• How to calculate the difference in days between two dates in various ways, such as calculating calendar days, weekdays, or weekdays with holidays.
• How to use dynamic formulas and tables to automatically calculate the difference in days between two dates.

Using the knowledge and skills you gained from this article, you can calculate the difference of days between two dates in Excel easily and accurately, and make your table more informative and professional. Good luck!