How to Calculate Days Between Dates with Blank Cells in Excel
Question details
Calculate the number of days from today to an event date, or to a request date if the event date cell is left blank.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking event schedules or request timelines where some specific date fields might be incomplete or pending.
- Observed behavior
- A dynamic formula is required to calculate the date difference by seamlessly switching between the request date and the event date depending on cell availability.
Ensure that the cells containing your request dates and event dates are formatted as 'Date' rather than 'Text' to prevent formula calculation errors like #VALUE!.
Use the IF Function Combined with TODAY()
This is the most efficient method to automatically check if the event date cell is blank and calculate the days based on the current system date.
The IF function allows Excel to test a condition (such as whether a cell is blank). By combining it with the TODAY() function, the formula dynamically updates the calculation every time you open the workbook.
Determine which cells hold your data. For example, assume B1 contains the Request Date and C1 contains the Event Date.
Click on the cell where you want the calculated number of days to appear.
Type the formula: =IF(C1="", B1-TODAY(), C1-TODAY()) into the formula bar.
Press Enter to execute the formula. If C1 is empty, it calculates B1 minus today's date; otherwise, it calculates C1 minus today's date.
Use a Static Reference Cell for the Start Date
If you need to calculate the days from a specific fixed date rather than the current system date, use a dedicated reference cell.
Calculate Complex Dates Easily with WPS Spreadsheet
WPS Spreadsheet fully supports over 300 built-in formulas, including logical and date functions like IF and TODAY. It provides an intuitive interface for creating dynamic calculations without compatibility issues.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your dates.
- 2. Select the target cell: Click on the empty cell where you want the calculated days to show up.
- 3. Initiate the formula: Type the '=' sign to activate the formula input mode.
- 4. Apply the logic: Enter your IF and TODAY combination formula and press the Enter key.
- 5. Batch apply the calculation: Drag the fill handle at the bottom right of the cell down the column to apply the formula to all other rows.
Frequently Asked Questions
Why am I getting a #VALUE! error when calculating dates?
This error typically occurs if one of your date cells contains text instead of a valid date format. To fix this, select the cells, right-click, choose 'Format Cells', and ensure the 'Date' category is applied.
How do I leave the result blank if both the request date and event date are blank?
You can use a nested IF function combined with the AND function. Try this formula: =IF(AND(B1="", C1=""), "", IF(C1="", B1-TODAY(), C1-TODAY())). This checks if both cells are empty first and returns a blank result.
Can I use the DATEDIF function with this IF logic?
Yes, you can wrap DATEDIF inside the IF function to calculate months or years instead of days. For example, to calculate months: =IF(C1="", DATEDIF(TODAY(), B1, "m"), DATEDIF(TODAY(), C1, "m")).
