Using date validation with default text

[bobsie]

Hi all. I want to ensure users are putting valid dates into a column, say A2:A100.
I would like a default text entry of 'Not Known' to be valid as well in A2:A100.

Date and text validation is not that straightforward, can anyone help? Thanks.

 

[JoeMo]

You didn't tell us which dates are valid, but here's an example you can adapt to your dates. In this example I want to limit dates to the current year (2018).

1. Select A2:A100
2. Data>Validation>Custom
3. Enter this formula in the dialog box:

=OR(A2="Not Known",AND(A2>=DATE(2018,1,1),A2<=DATE(2018,12,31)))

 

[bobsie]

You didn't tell us which dates are valid, but here's an example you can adapt to your dates. In this example I want to limit dates to the current year (2018).

1. Select A2:A100
2. Data>Validation>Custom
3. Enter this formula in the dialog box:

=OR(A2="Not Known",AND(A2>=DATE(2018,1,1),A2<=DATE(2018,12,31)))

That is a huge help, much appreciated!

 

[JoeMo]

That is a huge help, much appreciated!

You are welcome - thanks for the reply.