Sign in to join the conversation:
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?
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!
Hello, I face one difficulty trying to transform text in date using DATE formula I want to find the first day of a week based on the week number & year number put in a cell For instance in a cell I find "25-45" FOR Year= 2025 & Week= 45, so I look for the monday of this week that should be the 3rd of nov Step 1 find first…
I have a formula that I have used for years on a sheet and it was working an hour ago and then all of a sudden it just stopped working, I went out the sheet and back in and . The formula is =IF([Shop Order]@row = 0, (COUNTIF((RN:RN), RN@row ))) but instead of returning the value it says #INVALID COLUMN VALUE Got it working…
Hi Everyone, Looking for some assistance on a dashboard report issue I am running into. I have a dashboard with 3 reports added as Web Content. All 3 reports use at 1 common sheet. All 3 reports are published as read only. Users are not shared the underlying sheets. All 3 reports have some form of filtering. 2 of the…
