Insert Current Date and Time When an Excel Cell Is Blank in WPS Spreadsheets
Question details
The user needs a formula to automatically insert the current date and time when a specified cell is blank in an Excel-compatible spreadsheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is building a spreadsheet and wants a timestamp to appear only when a target cell is empty.
- Observed behavior
- The CELL function cannot directly test whether a cell contains a value, so a formula must use ISBLANK with NOW.
Open the WPS Spreadsheets workbook and identify the cell to test. Confirm the target cell reference is correct before entering the formula.
Use ISBLANK with NOW to Return Date and Time for a Blank Cell
Use this formula when you want the current date and time to appear automatically whenever a chosen cell is empty.
The ISBLANK function returns TRUE only when the referenced cell has no content. The NOW function is volatile, so the displayed date and time refreshes when the workbook recalculates.
Open WPS Spreadsheets and click the cell where the date and time should appear.
Type =IF(ISBLANK(A1),NOW(),"") in the formula bar, replacing A1 with the cell you want to test.
Press Enter, then use Home > Number Format to set the result cell to a date and time format if needed.
Use WPS Spreadsheets to Auto-Fill Date and Time for Blank Cells
Open your workbook in WPS Spreadsheets and use the ISBLANK and NOW functions to display the current date and time only when a referenced cell is empty.
- 1. Open your workbook: Launch WPS Office and open the Excel-compatible workbook in WPS Spreadsheets.
- 2. Select the result cell: Click the cell where you want the date and time result to appear.
- 3. Enter the formula: Type =IF(ISBLANK(A1),NOW(),"") in the formula bar, replacing A1 with your target cell.
- 4. Format the cell: With the cell selected, go to Home > Number Format and choose a date and time format.
- 5. Test the result: Leave the referenced cell blank to see the current date and time, or enter a value to make the result empty.
Frequently Asked Questions
Why does the date and time keep updating after I enter the formula?
NOW() is a volatile function. It recalculates whenever any change or recalculation occurs in WPS Spreadsheets. If you need a fixed timestamp, use Ctrl+; for the date and Ctrl+Shift+; for the time.
Can I test a blank cell in another sheet?
Yes. Reference the sheet and cell in the ISBLANK function, for example =IF(ISBLANK(Sheet2!A1),NOW(),"").
How do I show only the date, not the time?
Use =IF(ISBLANK(A1),TODAY(),"") instead of NOW, or apply a date-only number format to the result cell.
What should I do if the formula returns #####?
Increase the column width or change the number format to a date and time format so the result can display correctly.
