Function Problems

How to Count Days from a Start Date Until Completion in Excel

Elise Williams Oct 14, 2026 870 views

Question details

The user needs an Excel formula to count the days from a start date to a completion date, defaulting to today's date if the completion date is blank, and keeping the cell blank if the start date is empty.

How to Count Days from a Start Date Until Completion in Excel

Product: Excel, WPS Spreadsheet
Device & OS: not provided
Scenario: Tracking project timelines, task durations, or aging reports where tasks may be ongoing or already completed.
Observed behavior: A dynamic numerical count of days is displayed based on whether start and completion dates are present.

Before you start

Ensure that the cells containing your start and completion dates (e.g., E10 and F10) are properly formatted as 'Date' to prevent calculation errors.

Solution 1Recommended

Use a Simple Subtraction Formula with IF and TODAY

This is the most straightforward approach, using basic subtraction combined with IF functions to dynamically handle blank cells and ongoing tasks.

In Excel, dates are stored as sequential serial numbers, meaning you can subtract an earlier date from a later date to find the difference in days.

Select the target cell

Click on the cell where you want the calculated day count to appear.

Enter the subtraction formula

Type the formula =IF(E10="","",IF(F10="",TODAY(),F10)-E10) into the formula bar. Replace E10 with your start date cell and F10 with your completion date cell.

Apply the formula

Press Enter to calculate the difference. This subtracts the start date from either the completion date or today's date if the completion date is blank.

Format as Number

Right-click the result cell, select 'Format Cells', choose the 'Number' category, and set decimal places to 0 to display a whole number instead of a date format.

Use a Simple Subtraction Formula with IF and TODAY

Dynamic Updates: Because this formula uses the TODAY() function, the day count for uncompleted tasks will automatically increase each day without any manual updates.

Solution 2

Calculate the Duration Using the DATEDIF Function

Use the built-in DATEDIF function specifically designed for calculating the exact duration between two dates.

Efficient Spreadsheet Management

Calculate Dates Effortlessly with WPS Spreadsheet

WPS Spreadsheet supports all standard date and time functions, including DATEDIF, IF, and TODAY. You can easily track project durations and timelines with an interface similar to Excel.

1. Open your file: Launch WPS Spreadsheet and open your project tracking document.
2. Input the formula: Select the duration column and paste the =IF(E10="","",IF(F10="",TODAY(),F10)-E10) formula.
3. Format the output: Right-click the cell, select 'Format Cells', and change the format to 'Number' for a clean day count.

Fully compatible with Microsoft Excel date formulasFree, lightweight, and fast spreadsheet solutionBuilt-in cell formatting tools for dates and numbersCross-platform support for Windows, Mac, and mobile devices

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a date like 1/15/1900 instead of a number?

Excel and WPS Spreadsheet sometimes automatically format the result of a date-based calculation as a date. To fix this, right-click the cell, select 'Format Cells', and change the formatting from 'Date' to 'General' or 'Number'.

Does the TODAY() function update automatically?

Yes, the TODAY() function is volatile and recalculates the current date automatically every time you open the workbook or whenever a sheet recalculation occurs.

Can I count only working days instead of total calendar days?

Yes. To calculate only business days, use the NETWORKDAYS function instead of subtraction. For example, use =IF(E10="","",NETWORKDAYS(E10, IF(F10="", TODAY(), F10))) to automatically exclude weekends.

Excel & Data Problems