Calculation Issues

How to Calculate Project Days and Stop When Complete in Excel

Maira Mehtab Sep 24, 2026 869 views

Question details

The user needs a formula to calculate the number of days a project is in process starting from an employee arrival date, and to stop counting when a visa issued date is entered, displaying total completion days only when both dates are present.

Product: Excel
Device & OS: not provided
Scenario: Tracking project timelines or employee processing durations based on starting and completion dates.
Observed behavior: Requires a dynamic calculation that updates ongoing days automatically but locks in the final duration once an end date is provided, hiding zero values as hyphens.

Before you start

Ensure that your start date and end date columns are properly formatted as 'Date' in Excel so the formulas can correctly calculate the mathematical difference between them.

Solution 1Recommended

Use IF Formulas and Custom Formatting for Elapsed Days

Combine the IF function with simple subtraction to check if the end date is entered, and apply custom cell formatting to elegantly display zero values as hyphens.

By utilizing the IF function, Excel can verify whether the completion date cell is blank. If it is blank, it can use the TODAY() function to calculate ongoing days. If it is populated, it subtracts the start date from the end date.

To keep the spreadsheet clean, applying a custom number format will ensure that any 0 values (where both dates might be missing) appear as hyphens instead of zeros.

Set up your date columns

Assuming Column A contains the 'Employee Arrival Date' and Column B contains the 'Visa 18 Issued Date'.

Calculate Days in Process

In Column C, enter the formula `=IF(ISBLANK(B2), TODAY()-A2, B2-A2)`. This calculates the ongoing days if Column B is empty, or the final elapsed days if a date is entered.

Calculate Days to Complete

In Column D, enter the formula `=IF(ISBLANK(B2), 0, B2-A2)`. This ensures that the days to complete are only calculated when the Visa Issued date is actually present, otherwise returning 0.

Apply Custom Formatting

Select the cells in Columns C and D, right-click, and choose 'Format Cells'. Navigate to the 'Custom' category.

Enter the custom format code

In the Type box, enter `General;General;"-"` and click OK. This formatting tells Excel to display positive and negative numbers normally, but replace zero values with a hyphen.

Handling Blank Start Dates: If you want to prevent calculations when the start date (Column A) is also blank, you can wrap your formula in another IF statement: `=IF(ISBLANK(A2), "", IF(ISBLANK(B2), TODAY()-A2, B2-A2))`.

Project Management Made Easy

Calculate Project Durations Seamlessly in WPS Spreadsheet

WPS Spreadsheet provides powerful date and time functions fully compatible with Microsoft Excel, making project tracking and duration calculation incredibly easy and fast. Manage large datasets without lag and utilize built-in custom formatting.

1. Open your project tracker: Launch WPS Spreadsheet and open the document containing your project start and end dates.
2. Apply the conditional formula: Enter the formula `=IF(ISBLANK(B2), TODAY()-A2, B2-A2)` in your duration column to accurately calculate ongoing or completed days.
3. Format zero values: Press `Ctrl + 1` to open the Format Cells dialog, go to Custom, and input `General;General;"-"` to hide zero values.

100% compatible with Microsoft Excel date formulas and cell formats.Lightweight software with fast calculation speeds for large project tracking sheets.Intuitive formatting tools to easily manage zero values and custom cell styles.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a #VALUE! error when subtracting dates?

This error typically occurs if your date cells contain text instead of valid Excel dates. To fix this, select your date columns, go to the Home tab, and ensure the Number Format is set to Short Date. You may need to re-enter the dates if they were typed as text.

How can I exclude weekends from my project day calculation?

Instead of using simple subtraction (B2-A2), use the NETWORKDAYS function. For example, `=NETWORKDAYS(A2, B2)` calculates the total number of working days between two dates, automatically excluding weekends.

How do I calculate days excluding specific holidays?

You can use the NETWORKDAYS function with an optional holiday range. Create a list of holiday dates in your sheet (e.g., cells H2:H10), and use the formula `=NETWORKDAYS(A2, B2, H2:H10)`. This subtracts both weekends and the specified holidays from the total days.

Excel & Data Problems