Calculation Issues

How to Calculate Approval Days and Pending Files in Excel

Kushani Nimanthika Sep 27, 2026 871 views

Question details

Create an Excel tracker to calculate the number of days between an assigned date and an approved date, leaving unapproved records blank, while also counting pending files for each employee.

How to Calculate Approval Days and Pending Files in Excel

Product: Excel
Device & OS: not provided
Scenario: Setting up a worksheet to track employee task assignments, measure how long approvals take, and monitor unresolved workloads.
Observed behavior: The goal is to dynamically output the number of elapsed days when an approval date is logged, leave the cell blank if unapproved, and automatically count the remaining pending files per employee.

Before you start

Ensure your 'Date Assigned' and 'Date Approved' columns are properly formatted as Dates in Excel, otherwise, the mathematical subtraction formula will return an error.

Solution 1Recommended

Use IF and COUNTIFS Formulas to Track Approvals and Pending Files

Use the IF function to calculate elapsed days only when an approval date exists, and use COUNTIFS to count blank approval dates for specific employees.

To calculate elapsed days without showing errors for incomplete tasks, you can use the IF function to check if the approval date cell is blank. To count pending files, the COUNTIFS function allows you to count rows matching a specific employee where the approval date is still empty.

Calculate elapsed approval days

Assuming 'Date Assigned' is in cell A2 and 'Date Approved' is in cell B2, click on cell C2 and enter the formula: =IF(B2="","",B2-A2). Press Enter. This leaves C2 blank if there is no approval date, or calculates the difference if there is.

Format the result as a number

Select cell C2, navigate to the 'Home' tab, click the 'Number Format' dropdown in the ribbon, and select 'Number' or 'General' to ensure it displays as days rather than a date.

Count pending files per employee

Assuming employee names are in column A, approval dates are in column B, and the specific employee you want to evaluate is in cell D2. Click on an empty cell (e.g., E2) and enter the formula: =COUNTIFS($A$2:$A$100,D2,$B$2:$B$100,""). Press Enter.

Apply formulas to the entire column

Click on the bottom-right corner of cell C2 (the fill handle) and drag it down to apply the days calculation formula to all records in your tracker.

Use IF and COUNTIFS Formulas to Track Approvals and Pending Files

Dynamic Tracking: As you add new assignments or fill in approval dates, these formulas will automatically update your elapsed days and pending file counts.

Free Microsoft Office alternative

Easily Track Employee Tasks and Approvals in WPS Spreadsheet

WPS Spreadsheet fully supports advanced logical and counting formulas like IF and COUNTIFS. You can build professional task trackers for free with a familiar interface, keeping you productive without a steep learning curve.

1. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to create a new blank workbook or open your existing tracker.
2. Enter your formulas: Type the exact same =IF() and =COUNTIFS() formulas into the formula bar to calculate days and count pending files.
3. Format data effortlessly: Use the intuitive ribbon under the 'Home' tab to format your cells as Dates or Numbers with a single click.
4. Save and share: Save your document in .xlsx format to ensure seamless sharing and compatibility with teammates using Microsoft Excel.

Fully compatible with Microsoft Excel (.xlsx) formats and standard formulasEasily calculate dates, elapsed time, and employee workloadsFree and lightweight with a familiar tabbed interfaceBuilt-in templates for task tracking and project management

microsoft office alternative - wps office

Frequently Asked Questions

Why is my elapsed days calculation showing as a weird date instead of a number?

When you subtract one date from another, Excel sometimes assumes the result should also be a date. To fix this, right-click the cell, select 'Format Cells', and change the category to 'Number' or 'General'.

How do I exclude weekends from the approval days calculation?

If you only want to count business days, use the NETWORKDAYS function instead of basic subtraction. Update your formula to: =IF(B2="","",NETWORKDAYS(A2,B2)).

Can I highlight pending files that have been waiting for more than 7 days?

Yes, you can use Conditional Formatting. Select your data range, click 'Conditional Formatting' > 'New Rule' > 'Use a formula to determine which cells to format', and enter =AND(B2="", TODAY()-A2>7). Set a fill color like red to highlight overdue pending files.

Why does my COUNTIFS formula return 0 even when there are pending files?

This usually happens if the cell isn't truly blank. Ensure there are no hidden spaces in your 'Date Approved' column. Also, check that your ranges in the COUNTIFS formula are locked with absolute references (like $A$2:$A$100) so they don't shift when copying the formula.

Excel & Data Problems