validating cell value

amedina52556

Hi, I have a sheet with diferent columns, one is "Initial Date" and other one is "Ending date".

How can avoid users update "Ending Date" with a date earlier than today?

Paul Newcome

Will the Initial Date ALWAYS be before the Ending Date?

amedina52556

not always.

Thanks in advance Paul!

Paul Newcome

Of course not. You can't make things easy can you? Haha. I'll guess work on it anyway though.... 

Paul Newcome

Unfortunately there isn't a way to STOP someone from entering an earlier date, but there are ways to bring to their attention that they have done so.

 

You are going to need 3 helper columns, one of which will require additional user input. I know I know... You're going to hear some griping that people have to do one more step... We will also be using conditional formatting.

 

The downside to the TODAY() function is that it will always be Today. That means when you open it next week, all of those dates that WERE good, are now less than today. So to avoid that, we are going to have the user check a box once they get a "Valid Date" message.

 

The screenshots below should help explain exactly what we are doing. Just ignore the gibberish to the left and right. I have a sheet specifically for testing stuff for the Community and don't always clean up before testing something new. On to the fun stuff...

 

First helper column we will call "Help Text". This column will be blank if there is no End Date or if the End Date has been validated already. Plug the following in there:

 

=IF(ISBLANK([Ending Date]@row), "", IF([Valid?]@row = 1, "", IF(AND([Valid?]@row = 0, Helper@row = 0), "------------End Date Valid. Please Check Box to Right -------------------->", IF(AND([Valid?]@row = 0, Helper@row = 1), "End Date Invalid. Please Select A Date That Is Not Less Than Today."))))

 

The bold portion can be changed to whatever text you would like it to say whether the date is valid or not.

 

The second helper column will be a simple checkbox column that we will call "Valid?". That's all that one is.

 

The third helper column is also a checkbox column, but it can be shoved all the way to the end of the sheet and "hidden". We can call that one "Helper". In that column you will enter:

 

=IF(AND(ISDATE([Ending Date]@row), [Ending Date]@row < TODAY(), [Valid?]@row = 0), 1, 0)

 

Then enter some conditional formatting based on the screenshots below, and here is what will happen...

 

First the "Helper" column will read the "Ending Date" column as well as the "Valid?" column. If the Ending Date has not already been validated AND it is before today, the box will be checked. If that box is checked, the error message will appear in the Help Text column instructing them to enter a valid date. Once a valid date is entered, the Help Text column will display the valid message along with instructions to check the Valid? box. Once that box has been checked, validation will stop.

 

Also built into all of this is that the validation process does not start until an Ending Date is entered. That prevents blank ending dates from displaying as invalid. I have also included that once an Ending Date has been checked as valid, the Help text field will return to blank. That will help keep your sheet clean.

 

Of course you could also choose to have the help text column display whatever text you want such as "Date Validated" once the validation box is checked. I was just trying to keep it (relatively) simple for now.

 

HOPEFULLY this gives you what you need. Of course you are more than welcome to ask further questions or for clarification on anything.

amedina52556

Thanks Paul!!

I appreciate your help.

Regards!