Calculate Days From a Start Date to Today While Ignoring Blank Cells
Question details
The user needs to calculate the elapsed days from a given start date to the current date or a fixed date, but requires the formula to return a blank result if the source date cell is empty.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Calculating elapsed time in a spreadsheet tracker where some start dates are not yet filled in, preventing calculation errors or false high numbers.
- Observed behavior
- Standard date subtraction returns an incorrect large number or an error when the source cell is blank. The goal is to return a clean, empty string instead.
Ensure your start dates are formatted as actual Date values in your spreadsheet, and identify the specific column containing these dates before applying the formula.
Use IF and TODAY Functions to Calculate Elapsed Days
Combine the IF function with the TODAY function to calculate elapsed days dynamically while leaving the cell blank if no start date is provided.
In spreadsheet logic, a blank cell evaluates to zero, causing normal date subtraction to calculate the difference from the system's baseline date (usually in the year 1900). By wrapping your calculation in an IF function, you can check if the source cell is empty first.
Click on the empty cell where you want the calculated elapsed days to appear (for example, cell G9).
Type the formula =IF(H9="","",TODAY()-H9) into the formula bar, assuming your start date is located in cell H9.
Press Enter. Right-click the result cell, choose 'Format Cells', and set the category to 'Number' or 'General' so it displays as an integer rather than a date.
Click and drag the small square (fill handle) at the bottom right of the selected cell down the column to apply the formula to other rows.
Calculate Days to a Specific Fixed Date
Use the DATE function if you need to measure days elapsed up to a specific deadline or historical date instead of the current day.
Calculate Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced date formulas, including IF, TODAY, and DATE. You can efficiently manage your time trackers, schedules, and elapsed days calculations with ease.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the start dates.
- 2. Input the formula: Click on the destination cell and type =IF(H9="","",TODAY()-H9).
- 3. Get instant results: Press Enter to instantly see the calculated days, or a clean blank cell if the date is missing.
Frequently Asked Questions
Why does my formula return a weird date instead of a number of days?
This happens because the spreadsheet automatically formatted the result cell as a Date. To fix it, right-click the cell, select 'Format Cells', and change the category to 'Number' or 'General'.
How do I calculate only working days instead of all calendar days?
To calculate only business days between two dates while ignoring blanks, use the NETWORKDAYS function wrapped in an IF statement. The formula looks like this: =IF(H9="","",NETWORKDAYS(H9,TODAY())).
Can I use the DATEDIF function instead of simple subtraction?
Yes, you can use the DATEDIF function to find the difference in days. The formula would be =IF(H9="","",DATEDIF(H9,TODAY(),"d")). It achieves the exact same result as subtraction.
