How to Return Text from One Cell When Another Contains a Date in Excel
Question details
The user needs a formula to display text from a specific cell only when an adjacent cell contains a date, keeping the result blank if the date cell is empty.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a project or customer tracker where a customer name should automatically appear only if a completion date has been entered in another column.
- Observed behavior
- The user is looking for the correct logical formula to conditionally extract the customer name based on the presence of a date without showing errors or zeroes.
Ensure your spreadsheet has a dedicated column for dates and another for the text you want to pull. Verify that the empty date cells are truly blank and do not contain hidden spaces.
Use the IF Function to Check for Blank Cells
The simplest and most effective way to display text based on another cell's content is by using Excel's built-in IF function to check if the target cell is empty.
By utilizing the IF function, you can set a logical test that determines whether the cell containing the date is blank. If it is blank, you can command Excel to return an empty string. If it is not blank (meaning a date has been entered), Excel will pull the text from your desired cell.
Click on the cell (e.g., J13) where you want the customer name to appear conditionally.
Type the formula `=IF(G13="","",A13)`, replacing G13 with your date cell reference and A13 with your text cell reference.
Press Enter. If the date cell is empty, the formula cell will also appear empty. If a date is present, it will show the customer name.
Click the fill handle in the bottom-right corner of the cell and drag it down to apply this logic to the rest of the rows in your tracker.
Extract Text from Every Nth Row Using IF and OFFSET
If your data is structured with spaces between records (e.g., you need to extract data from every 3rd row), you can combine the IF function with the OFFSET function.
Master Logical Formulas with WPS Spreadsheet
Easily apply IF and OFFSET conditional formulas using WPS Spreadsheet. It offers a highly compatible and intuitive interface to manage your project trackers and extract data effortlessly.
- 1. Open your file: Launch WPS Spreadsheet and open the workbook containing your project data.
- 2. Input the formula: Select the target cell and enter the `=IF(G13="","",A13)` conditional formula.
- 3. Auto-fill your column: Press Enter and double-click the fill handle to seamlessly populate the entire column automatically.
- 4. Save your progress: Save your document in .xlsx format to ensure full cross-platform compatibility.
Frequently Asked Questions
Why does my IF formula return a 0 instead of a blank cell?
If your formula is written as `=IF(G13="", ,A13)` without the empty quotes `""`, the spreadsheet defaults to returning a 0 for the empty condition. Ensure you strictly type `""` in the 'value_if_true' argument to return a completely blank cell.
Can I make the IF formula check specifically for dates rather than just non-blank cells?
Yes, although spreadsheets lack a native ISDATE function. Because dates are stored as serial numbers, you can check if the cell contains a number using `=IF(ISNUMBER(G13), A13, "")`. This ensures text entries in the date column won't accidentally trigger the formula.
What if I want to return data from a completely different worksheet?
You can reference another sheet by adding the sheet name followed by an exclamation mark before the cell reference. For example: `=IF(Sheet1!G13="","",Sheet1!A13)` will check the blank status on Sheet1 and pull the text from Sheet1 to your current active sheet.
