Excel Formula to Check Whether a Task Was Completed on Time
Question details
The user needs an Excel formula to compare a task completion date against a specific deadline calculated from an initial start or hire date.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Tracking employee task completion times against differing deadline rules, such as exactly 90 days or three calendar months.
- Observed behavior
- Requires a conditional output (like Yes/No) depending on whether the completion date falls within the calculated deadline timeframe.
Ensure that your hire dates and completion dates are formatted as actual dates in Excel (not text) so the calculation functions can process them correctly.
Calculate Deadline Based on Three Calendar Months
Use the EDATE function when your deadline must fall on the exact same day of the month, three months later.
A calendar month calculation means that if the start date is July 18, the deadline will be October 18, regardless of how many exact days are in those months.
Click on the cell where you want the 'Yes' or 'No' result to appear.
Type the formula =IF(B6<=EDATE(A6,3),"Yes","No"), making sure to replace A6 with the cell containing your hire date, and B6 with the cell containing the completion date.
Press Enter to see the result, then drag the fill handle down to apply the formula to the rest of your list.
Calculate Deadline Based on Exactly 90 Days
Use a simple addition formula if your company policy strictly measures the deadline as 90 days from the start date.
Easily Manage Dates and Formulas with WPS Spreadsheet
WPS Office includes a highly capable Spreadsheet application that perfectly supports Excel date functions like EDATE and IF, making it simple to track deadlines and tasks for free.
- 1. Open your tracking sheet in WPS Spreadsheet: Launch WPS Office and open your task or employee tracker spreadsheet.
- 2. Format your date columns: Highlight your dates, right-click, select 'Format Cells', and ensure the 'Date' format is applied.
- 3. Input the conditional formula: Select the status cell and enter your preferred IF and EDATE formula to check the deadline.
- 4. Drag to fill: Click and hold the small square at the bottom-right corner of the cell and drag it down to apply the check to all employees.
Frequently Asked Questions
Why does my date formula return a #VALUE! error?
This usually occurs if the start date or completion date is recognized as text instead of a valid date format. Select your date cells, right-click to choose 'Format Cells', and change the format to 'Date'.
How do I calculate a deadline that is 6 months away instead of 3?
You can adjust the months parameter in the EDATE function. Use =EDATE(A6, 6) to calculate a date exactly six calendar months from the date found in cell A6.
Can I leave the cell blank if the task hasn't been completed yet?
Yes, you can nest another IF function to check for a blank completion date. For example: =IF(ISBLANK(B6), "Pending", IF(B6<=EDATE(A6,3), "Yes", "No")).
