How to Create an Excel Formula to Show Done, In Progress, or Not Started
Question details
The user wants to track activity progress automatically by displaying specific text statuses based on time comparisons against the current time.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a schedule or project task list where activities have a Start Time and End Time, requiring a dynamic status indicator.
- Observed behavior
- The user needs a reliable nested IF and AND formula referencing the current date and time to output "Done", "Presenting" (or "In Progress"), and "Not Started".
Ensure your start and end time columns are formatted correctly as Time or Date/Time values so the NOW() function can compare them accurately.
Use a Nested IF Formula with a NOW() Helper Cell
This method calculates the current time once in a helper cell, making your formula more efficient and easier to maintain across multiple rows.
Using a dedicated helper cell for the NOW() function prevents Excel from recalculating the current time for every single row independently, which drastically improves spreadsheet performance on larger documents.
Click on an empty cell at the top of your sheet (for example, A1) and enter =NOW() to generate the current date and time.
Locate the cells containing your activity's start time (e.g., A3) and end time (e.g., B3).
Click on your target status cell and type the formula: =IF(AND(A3<$A$1,B3<$A$1),"Done",IF(AND(A3<$A$1,B3>$A$1),"In Progress","Not Started")). You can replace "In Progress" with "Presenting" if preferred.
Ensure you use the dollar signs ($A$1) for the helper cell reference so it locks into place when you drag the fill handle to apply the formula to other rows.
Because the NOW() function is volatile, it only updates when the worksheet recalculates. Press F9 on your keyboard anytime you want to refresh the time and update the statuses manually.
Use an Inline NOW() Function for Smaller Datasets
If you only have a few rows of data, you can include the NOW() function directly inside the IF formula without needing a dedicated helper cell.
Track Task Statuses Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced logical functions like IF, AND, and NOW, allowing you to build dynamic task trackers, project schedules, and condition-based status dashboards efficiently.
- 1. Open your task list: Launch WPS Spreadsheet and open the workbook containing your project schedules or activity lists.
- 2. Apply the nested formula: Select the status column and enter the nested IF formula referencing your start time, end time, and current time cell.
- 3. Format the output visually: Navigate to the Home tab, click 'Conditional Formatting', and assign background colors (like Green for Done, Yellow for In Progress) to your text statuses.
- 4. Save as Excel format: Save your document in .xlsx format to ensure seamless sharing and collaboration with Microsoft Office users.
Frequently Asked Questions
Why isn't my task status updating automatically as time passes?
The NOW() function does not tick like a live clock; it only updates when the worksheet recalculates. You can force a recalculation across your workbook by pressing the F9 key, or simply by typing data into any cell.
Can I automatically color-code the cells based on Done, In Progress, or Not Started?
Yes, you can use Conditional Formatting. Highlight your status column, navigate to Home > Conditional Formatting > Highlight Cells Rules > Equal To, and assign specific font or fill colors for "Done", "In Progress", and "Not Started".
Why am I getting a formula error about too few or too many arguments?
This usually happens due to regional spreadsheet settings. Some regions require semicolons (;) instead of commas (,) to separate function arguments. Try replacing all commas in your formula with semicolons.
How do I handle tasks that do not have a defined end time yet?
You can modify your formula with an additional ISBLANK check. For example, you can nest IF(ISBLANK(B3),"No End Time", ...) at the beginning of your logic so it gracefully handles missing data without returning incorrect statuses.
