Clearing a Cell of an incorrect date

[azizrasul]

I don't want to use VBA as this will make it a xlsm file which can be rejected when it enters the company's email system.

I have date validated a cell so that only a current or greater date can be entered. However when an incorrect date is entered, a message appears but the incorrect date remains. Is their a way of clearing the cell if an incorrect date is entered?

 

[Special-K99]

I prefer the date to remain, that way the user can verify they did enter something incorrect.
If the date has disappeared the user cant check what they entered.
The date remaining also allows them to correct an incorrect date which may just be a single digit.
If the date has disappeared they have to reenter it all over again.

 

[Peter_SSs]

I don't want to use VBA as this will make it a xlsm file which can be rejected when it enters the company's email system.

I have date validated a cell so that only a current or greater date can be entered. However when an incorrect date is entered, a message appears but the incorrect date remains. Is their a way of clearing the cell if an incorrect date is entered?

Go back in to the Data Validation dialog for the Data validation cell and check your settings on the 'Error Alert' tab. If you choose 'Stop' in the 'Style' drop-down, it should not allow an invalid date to finally remain in the cell.

 

[azizrasul]

I couldn't get that to work Peter, the value IS a date but's it's <TODAY() instead of >=TODAY().

 

[Peter_SSs]

I couldn't get that to work Peter, the value IS a date but's it's <TODAY() instead of >=TODAY().

I'm not sure what you are saying here. So that I can try to replicate your problem, let's try to clarify ..

1. What cell are you trying to restrict the date entry in? If there are multiple cells, just give me one and answer the following in relation to that cell.
2. Select that cell and go back in to the Data Validation dialog. Detail all the settings on all the table in the Data Validation dialog. Then exit the DV dialog.

Now
3. Exactly what are you typing in the cell that you say leaves a date behind? Give one specific example.

 

[azizrasul]

I had placed more text in my last post but it only showed when I try to Edit but didn't show on the actual post. Hope that all I am typing will appear.

The cell in question is F4.
Allow: Date
Data: greater than or equal to
Start Date: =TODAY()

Nothing under Input Message

Under Error Alert
Style: STOP
Title: INCORRECT DATE
Error Message: Please enter a date that is greater than or equal to today's date

So if I type in say 01/07/18, this is a date that is in the past and I want the cell remain blank. However if I enter 06/07/18 which is today's date then that is fine.

The error message keeps appearring when an incorrect date is entered, which is fine, but instead of that i would prefer F4 to become blank and the error message to only appear once. Perhaps I'm asking a lot. LOL.

 

[Peter_SSs]

OK, I think I understand what you are saying. The DV allows the incorrect date temporarily in the cell if the user clicks Retry, but it won't allow in incorrect data to stay there when the user ties to move away from the cell.
You may be able to achieve what you want, or at least closer, with vba if that is acceptable. You have to be aware though that both Data Validation and vba can be defeated.
DV can be defeated if the user doe a copy/paste into the cell and vba is defeated if the user does not enable macros.

 

[azizrasul]

I ended up by using conditional formatting so that an incorrect date has a line through it and a correct date doesn't. As a compromise, although I want the cell to be blank if an incorrect date is entered, but at least this way the incorrect date has a line through so if the user saves the file in that way, they have only themselves to blame. I have also added an Input message so that if the user enters the cell, they cannot fail to see what the date requirements are.

Thanks for your help and getting me back on the forum. The email did eventually come through as spam.