How to Calculate Due Date with Formula in Excel (7 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we can easily calculate the due date of a project by using the DATE formula, EDATE Function, YEARFRAC Function, and WORKDAY Function. Today, in this tutorial, we’ll get to learn how we can calculate Due Date formula in Excel effectively with appropriate illustrations.


How to Calculate Due Date with Formula in Excel: 7 Suitable Ways 

Let’s say, we have a dataset where some Project Names and their Starting Date and Total Days to complete these Projects are given in Column B, Column C, and Column D respectively. In Column E, we’ll calculate the due date of these projects. To do this, we will use the DATE formula, IF function, and Conditional Formatting also. Here’s an overview of the dataset of our today’s task.

Due Date Calculation


1. Add Date to Calculate Due Date with Formula in Excel

Let, some Project Names and the starting date and Duration of these projects have been given in Column B, Column C, and Column D respectively. Here we want to calculate the due date of the project named Alpha, and then calculate the due date of other projects. Let’s follow the instructions.

Step 1:

  • First, select cell E5.

Add Date to Calculate Due Date Formula in Excel

  • After selecting cell E5, type the formula in Formula Bar. The formula is,
=C5+D5

Add Date to Calculate Due Date Formula in Excel

  • While completing the typing of the formula in Formula Bar, press Enter on your keyboard and you will get the due date, and the due date of the project named Alpha is June 4, 2018.

Add Date to Calculate Due Date Formula in Excel

Step 2:

  • Further, place the cursor on the Bottom-right in cell E5, and a Plus-sign(+) pops up. Then drag it downward.

Add Date to Calculate Due Date Formula in Excel

  • After completing the above steps, you’ll get your desired output in Column E which has been given in the screenshot.

Add Date to Calculate Due Date Formula in Excel

Read More: How to Copy Same Date in Excel


2. Apply the DATE Function to Calculate Due Date in Excel

In this method, we’ll learn how to calculate due dates in Excel by using the DATE Function. In our dataset, Year, Month, and Days have been given in Column B, Column C, and Column D respectively. Follow the steps below to learn!

Steps:

  • First, select cell E5.

Apply DATE Function to Calculate Due Date in Excel

  • Then type the formula in Formula Bar. the formula is,
=DATE(B5, C5, D5)

Apply DATE Function to Calculate Due Date in Excel

  • After typing the formula in Formula Bar, press Enter on your keyboard and you will get the due date, the due date is August 31st, 2021.
  • Then, place the cursor on the Bottom-right in cell E5, and a Plus-sign(+) pops up. Then drag it downward.

Apply DATE Function to Calculate Due Date in Excel

  • After that, you’ll get the due dates that have been given in the screenshot.

Apply DATE Function to Calculate Due Date in Excel

Read More: How to Use IF Formula with Dates


3. Use the Conditional Formatting to Calculate Due Date in Excel

After calculating the Due Dates in the above methods, now we’ll find out the due date which projects have been done till Today(January 11, 2022) by using Conditional Formatting. Suppose, we have a dataset where Project Names, the starting dates, and Due Dates are given in Column B, Column C, and Column D respectively. Let’s follow these steps to learn!

Step 1:

  • First, select cell D5 to cell D11.

3. UsConditional Formatting to Calculate Due Date in Excel

  • After selecting cells, from your Home Tab, go to,

Home → Styles → Conditional Formatting → New Rule

Conditional Formatting

Step 2:

  • Then the New Formatting Rule dialog box pops up. From that dialog box, go to,

Format only cells that contain → Format only cells with

  • In Format only cells with dialog box, firstly select Cell Value then go to next column and select less than or equal to and finally in the next column type the below formula.

=TODAY()

Conditional Formatting

  • Now, press Left-Click on your Mouse on the Format Then, a new dialog box named Format Cells pops up. From that Format Cells dialog box, go to,

Fill → Light Orange Color → Ok

Conditional Formatting

Step 3:

  • After that, you’ll go back to the first dialog box named New Formatting Rule, and press the OK from that dialog box.

Conditional Formatting

  • After pressing OK, you’ll get the due dates that have been completed up to Today(January 11, 2022). The completed projects have been shown below screenshot by using Conditional Formatting.

Conditional Formatting

Read More: How to Add Time in Excel Automatically


4. Apply the IF Function to Calculate Due Date in Excel

In this method, we’ll learn how to calculate the completed projects till Today(January 11, 2022). By using the IF Function, we can easily calculate the completed projects up to today. Let’s follow the steps.

Steps:

  • In cell E5, type the conditional IF Function. The IF Function is,

=IF(D5 < TODAY(), “Done”, “Not Done”)

Apply the IF Function to Calculate Due Date in Excel

  • After typing the conditional IF Function, press Enter on your keyboard and you will get the return of the function. The return of the function is Done.
  • Now, place the cursor on the Bottom-right in cell E5, and a Plus-sign(+) pops up. Then drag it downward.

Apply the IF Function to Calculate Due Date in Excel

  • Finally, you’ll get your desired output in Column E which means that the project has been Done or Not Done.

IF Function

Read More: Making a List of Countries by Time Zone in Excel


5. Insert the EDATE Function to Calculate Due Date in Excel

Here, we’ll calculate the due date formula in excel by using the EDATE function. Let’s say, we have the starting Date of some projects and their duration in terms of Months that provides in Column B, and Column C respectively. For this, Let’s follow the instructions.

Step 1:

  • First, select cell D5 and type the EDATE function. The EDATE function is,

=EDATE(B5, C5)

Insert the EDATE Function to Calculate Due Date in Excel

  • After typing the function in Formula Bar, press Enter on your keyboard and you will get the return of the function. The return is 43195.

Insert the EDATE Function to Calculate Due Date in Excel

  • Now, we’ll convert the 43195 number into a date. From your Home Tab, go to,

Home → Number → Short Date

Insert the EDATE Function to Calculate Due Date in Excel

  • After following the above step, we’ll be able to convert the number into a date.

Insert the EDATE Function to Calculate Due Date in Excel

Step 2:

  • Then, place the cursor on the Bottom-right in cell D5, and a Plus-sign(+) pops up. Then drag it downward.

Insert the EDATE Function to Calculate Due Date in Excel

  • After completing the above steps, we’ll get the due date of the projects in Column D by using the EDATE function.

Insert the EDATE Function to Calculate Due Date in Excel

Read More:  How to Create World Time Zone Clock in Excel


6. Apply EDATE and YEARFRAC Formula to Calculate Due Date in Excel

After learning the above methods, we’ll learn in this method how to calculate the Due Date by using the EDATE function and YEARFRAC function. Let’s say, we have a dataset where some Dates of Birth are given in Column B. By using the EDATE function, we’ll calculate the resignation date of the corresponding birthdays, and then calculate the number of years from birthdays to resignation date. Let’s follow the steps below.

Steps:

  • In cell C5, type the EDATE function and the function is,
=EDATE(B5, 12*65)
  • Where B5 is the Date of Birth and 12 is the Month.

EDATE and YEARFRAC Function

  • Now, press Enter on your keyboard and you’ll get the return value of the EDATE function. The return value is April 5, 2050.
  • After that select cell D5 and in Formula Bar type the YEARFRAC function. The YEARFRAC function is,

=YEARFRAC(B5, C5)

EDATE and YEARFRAC Function

  • Again, press Enter on your keyboard and you’ll get your desired output. The output is 65.

EDATE and YEARFRAC Function

  • Similarly, we can calculate the other due dates of the corresponding birthdate and the time difference between Birth Date and Due Date.

EDATE and YEARFRAC Function


7. Perform WORKDAY Function to Calculate Due Date in Excel

Let’s say, in our dataset the starting date of some projects and the working days are given in Column B and Column C. we can easily calculate the due dates of the projects that have been given in the screenshot by using the WORKDAY function. Follow the steps below to learn!

Step 1:

  • First of all, select cell D5.

Perform WORKDAY Function to Calculate Due Date in Excel

  • In the Formula Bar, type the WORKDAY function. The WORKDAY function is,

=WORKDAY(B5, C5)

  • Where cell B5 is the starting date of the project and cell C5 is the working Day of the project.

Perform WORKDAY Function to Calculate Due Date in Excel

  • After that, press Enter on your keyboard and you’ll get the return value of that function. The return value is August 3, 2018.

Step 2:

  • Hence, place the cursor on the Bottom-right in cell D5, and a Plus-sign(+) pops up. Then drag it downward.

Perform WORKDAY Function to Calculate Due Date in Excel

  • After completing the above steps, you will get your desired output in Column D that has been given in the below screenshot.

Perform WORKDAY Function to Calculate Due Date in Excel

Read More: How to Use Excel Date Shortcut


Things to Remember

👉 We can use the DATE function to calculate the due date.

👉 Another way is to use Conditional Formatting. For this, from your Home Tab, go to,

Home → Styles → Conditional Formatting → New Rule

👉To calculate the due date, we can also use EDATE, YEARFRAC, and WORKDAY functions.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

I hope all of the suitable methods mentioned above to calculate the due date will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.


Further Readings


<< Go Back to Date Range | Date-Time in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdur Rahim Rasel
Md. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo