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.
Method 1 – Add Days to Calculate the Due Date with Formula in Excel
Steps:
- Select cell E5.
- Insert the following formula:
=C5+D5
- Press Enter on your keyboard.
- Place the cursor on the bottom-right corner of cell E5, and a plus sign (+) pops up. This is the Fill Handle.
- Drag the Fill Handle down or double-click on it.
- Here’s the result.
Read More: How to Copy Same Date in Excel
Method 2 – Apply the DATE Function to Calculate the Due Date in Excel
In our dataset, Year, Month, and Days have been given in Column B, Column C, and Column D, respectively.
Steps:
- Select cell E5.
- Insert the following formula:
=DATE(B5, C5, D5)
- Hit Enter to get the first result.
- Drag down the Fill Handle or double-click on it to AutoFill.
- Here are the results of the dates.
Read More: How to Use IF Formula with Dates
Method 3 – Use Conditional Formatting to Calculate the Due Date in Excel
We have a list of due dates in column D and will highlight if they have passed (i.e. if they are before today’s date).
Steps:
- Select D5:D11.
- Go to Home, select Conditional Formatting, and choose New Rule.
- The New Formatting Rule dialog box pops up.
- Choose Format only cells that contain.
- In the Format only cells with dropdowns, select Cell Value and less than or equal to.
- Insert the following formula in the third box.
=TODAY()
- Select Format.
- A new dialog box named Format Cells pops up.
- Go to Fill and choose a color from the selector on the left (we chose a light orange).
- Hit OK.
- You’ll go back to the first dialog box.
- Press OK.
- All the due dates that have passed are highlighted.
Method 4 – Apply the IF Function to Calculate the Due Date in Excel
Let’s assume that any project with a due date in the past has been completed, so we’ll insert a text value in a new column to signal that.
Steps:
- In cell E5, use the following formula:
=IF(D5 < TODAY(), “Done”, “Not Done”)
- Press Enter.
- Drag the Fill Handle (the plus icon on the bottom-right corner of the cell) down.
- Here’s the entire column E.
Read More: Making a List of Countries by Time Zone in Excel
Method 5 – Insert the EDATE Function to Calculate Due Date in Excel
We have the starting Date of some projects and their duration in Months.
Steps:
- Select cell D5 and insert the following:
=EDATE(B5, C5)
- Press Enter on your keyboard and you will get the return of the function. The return is 43195.
- From your Home Tab, go to the Number format selector and select Short Date.
- This converts the value into a human-readable date.
- Hover over the bottom-right corner of D5 to get the Fill Handle (plus icon).
- Drag the Fill Handle down or double-click on it.
- Here’s the result.
Read More: How to Create World Time Zone Clock in Excel
Method 6 – Apply EDATE and YEARFRAC Functions to Calculate the Due Date in Excel
We have a list of dates of birth. We’ll calculate the date when the person turns 65 years old. Then we’ll use a different function to return the time difference between the dates in years.
Steps:
- In cell C5, insert the following formula:
=EDATE(B5, 12*65)We added 65*12 since we’re adding 65 years and the function works in months.
- Hit Enter.
- Select cell D5 and enter the following formula:
=YEARFRAC(B5, C5)
- Hit Enter. You should get 65.
- Fill the columns with the formulas. You can select C5:D5 and drag their collective Fill Handle down (from the bottom-right of D5).
Method 7 – Use the WORKDAY Function to Calculate the Due Date in Excel
We have a list of starting times for projects and how many workdays they will take to complete (Monday-Friday).
Steps:
- Select cell D5.
- Insert the following formula:
=WORKDAY(B5, C5)
- Hit Enter.
- Drag the Fill Handle of D5 down or double-click on it.
- Here’s the result.
Download the Practice Workbook
Further Readings
<< Go Back to Date Range | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
