validating cell value
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?
Comments
-
Will the Initial Date ALWAYS be before the Ending Date?
-
not always.
Thanks in advance Paul!
-
Of course not. You can't make things easy can you? Haha. I'll guess work on it anyway though....
-
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.
-
Thanks Paul!!
I appreciate your help.
Regards!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!