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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
7 Suitable Ways to Calculate Due Date with Formula in Excel
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.
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.
- After selecting cell E5, type the formula in Formula Bar. The formula is,
=C5+D5
- 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.
Step 2:
- Further, place the cursor on the Bottom-right in cell E5, and a Plus-sign(+) pops up. Then drag it downward.
- After completing the above steps, you’ll get your desired output in Column E which has been given in the screenshot.
Read More: How to Use the VBA DateAdd Function 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.
- Then type the formula in Formula Bar. the formula is,
=DATE(B5, C5, D5)
- 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.
- After that, you’ll get the due dates that have been given in the screenshot.
Read More: How to Use Excel Date Shortcut
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.
- After selecting cells, from your Home Tab, go to,
Home → Styles → Conditional Formatting → New Rule
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()
- 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
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.
- 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.
Read more: How to Format Date with VBA in Excel (4 Methods)
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”)
- 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.
- Finally, you’ll get your desired output in Column E which means that the project has been Done or Not Done.
Read more: How to Use IF Formula with Dates (6 Easy Examples)
Similar Readings
- Use Year Function in Excel VBA (5 Suitable Examples)
- How to Use Excel VBA MONTH Function (7 Suitable Examples)
- Use EoMonth in Excel VBA (5 Examples)
- How to Use the VBA DatePart Function in Excel (7 Examples)
- Use VBA DateSerial Function in Excel (5 Easy Applications)
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)
- 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.
- Now, we’ll convert the 43195 number into a date. From your Home Tab, go to,
Home → Number → Short Date
- After following the above step, we’ll be able to convert the number into a date.
Step 2:
- Then, place the cursor on the Bottom-right in cell D5, and a Plus-sign(+) pops up. Then drag it downward.
- After completing the above steps, we’ll get the due date of the projects in Column D by using the EDATE function.
Read More: Excel Date Picker for Entire Column
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.
- 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)
- Again, press Enter on your keyboard and you’ll get your desired output. The output is 65.
- Similarly, we can calculate the other due dates of the corresponding birthdate and the time difference between Birth Date and Due Date.
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.
- 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.
- 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.
- After completing the above steps, you will get your desired output in Column D that has been given in the below screenshot.
Read More: How to Use the Day Function in Excel VBA (3 Examples)
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.
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
- How to Insert Current Date in Excel (4 Suitable Examples)
- Date Variable in VBA Codes (7 Uses of Macros with Examples)
- Get the Current Date in VBA (3 Ways)
- How to Use VBA DateValue Function in Excel (6 Examples)
- VBA Date to String Conversion in Excel (6 Methods)
- Excel VBA to Find Week Number (6 Quick Examples)
- Now and Format Functions in Excel VBA (4 Examples)