CF if not a date

[azizrasul]

I have a .xlsx file that is being used as a template for surveyors to fill in. Hence it's NOT a macro enabled file.

I want the surveyor's to enter a date in cell G4 (which is formatted as a date, dd/mm/yyyy). How do I use conditional formatting so that if the surveyor does not enter a valid date, the cell G4 goes blank?

 

[steve the fish]

You would be better off using validation. Click the cell in question then Data tab-Data Validation-Allow-Date then add appropriate dates.

 

[azizrasul]

I'm having trouble with that, hence I thought CF might do the trick. But happy to go down the Data Validation route if it solves the problem.
In the DV I have selected Date and a start and end date. However if I put an incorrect date e.g. 21/08/201 It doesn't scream at me and just accepts the value. What am I doing wrong?

In cell A1 I have =TODAY()-1
In cell B1 I have =TODAY()+1

as my start and end dates.

 

[steve the fish]

How are you entering this date into the cell?

 

[Finalfight40]

When you go into your data vaidation and enter the start date and end date, there is a tab which is labelled error alert with a tick box "show error alert when invalid data is entered". Is that box ticked for you?

 

[azizrasul]

I go into the cell and type in the date or go into the formula bar and then type.
I didn't have the box ticked. Now that I have done so, it works great.

However I want cell G4 to go blank if an incorrect date is entered. Is that possible?

 

[Finalfight40]

Not as far as i know but i could be wrong and hopefully someone will correct me if i am.

I would probably use a custom input and error message saying something like "Please enter a valid date between yesterday and tomorrow or leave blank".

 

[steve the fish]

The whole point of validation is that you cannot type something in the cell that isnt validated.

 

[Finalfight40]

However I want cell G4 to go blank if an incorrect date is entered. Is that possible?

Just to confirm when you mentioned the point above, did you mean rather than it pop up an error box, it would just set the cell to blank? As that is how i understood it when i answered in post #7 .