Data Validation - Date and Text String

[tlc53]

Hi there,

I have Data Validation set on my date entry cells so only a valid date can be entered. The formula I am using is =AND(ISNUMBER(E158),LEFT(CELL("format",E158),1)="D") and this works fine.

However, when no date has yet been entered, I would like the cell to read "Enter Date". I tried formatting the formula to =AND(ISNUMBER(E158),LEFT(CELL("format",E158),1)="D")&ISTEXT("Enter Date") but this hasn't worked.

Any ideas how I can make the cell accept text string "Enter Date" and also only valid date?

Thank you!

 

[mrshl9898]

Hi, how's this?

=OR(AND(ISNUMBER(E158),LEFT(CELL("format",E158),1)="D"),E158="Enter Date")

 

[jtakw]

Hi,

A couple of different ways you can do this:

1. Just enter a Custom "Input Message" (i.e. Enter Date), so whenever E158 is selected the message will pop up.

2. Input "Enter Date" in E158 Before you apply your DV using your Original formula.

 

[tlc53]

BINGO! Thanks mrshl9898 - that did the trick