date field

[Helen Wills]

Hi
I am creating a project tracker where users will be collaborating in the same document. One of the fields requires inputting the date of the latest update.

How can i make the cell (O16), display either
[insert date here]. black text, grey background
or
display --/--/-- as a prompt black text, grey background

but then for the text to disappear and background to go to white when the date is populated (a bit like creating a form in word when you can insert fields...

 

[galbert]

Hi Helen,

You could use conditional formatting for this. Prepopulate the required field with the input text of your choosing (either [insert date here] or --/--/--) then apply a conditional formatting rule which changes the background colour of the given cell if it displays the input prompt. For example, if your prompt is [insert date here] in O16, highlight the cell, go to Home->Conditional Formatting->New Rule, then select "Format only cells that contain". Change the 2nd dropdown field from "between" to "equal to" and type "[insert date here]" in the last field (without quotation marks). Select the format you would like to apply by clicking on the "Format..." button. Click OK. Now the field will have the selected format as long as its content is the text you've defined. Once the users overwrite it, the formatting will revert to the original.

Hope this helps

 

[GWteB]

You could do that with conditional formatting (ribbon > home tab):
- New Rule
- Rule Type: Format only cells that contain
- Rule Description / Format only cells with: Specific Text - containing - [insert date here]
Click Format button to define colors of font and background (on Fill tab).
Finally [OK], [OK], [Apply], [OK]

PS: my locale is Dutch.

Book1
	A
1	[insert date here]
2	1-10-2020
Sheet1

Cell Formulas
Range	Formula
A2	A2	=NOW()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:A2	Cell Value	contains "[insert date here]"	text	NO

 

[Helen Wills]

It worked via conditional formatting - thank you!

 

[GWteB]

You are welcome and thanks for letting us know.