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