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!
From my research, I understand there isn't a way to keep formulas when exporting from Smartsheet into an Excel document. I have a total of 50 columns with formulas and would think there was a quicker way to grab the formulas. So far, I've appended a "!" which turns the formula into text which does export. However, I'm not…
I currently have 14 sheets with the following columns: Batch # and Reviewer I use an Index Distinct formula to acquire the unique batch numbers from all 14 sheets and put them into 14 columns on the 'metrics' sheet. I then use another index distinct to get a list of all the unique batch numbers into one 'Unique Batch…
Hello, I am looking for formula help where I want to return the earliest date in a range for different workstreams groups on a project. The source sheet is formatted as a date field, and the formula used below is returning a 0 no matter what I do. Any suggestions? =MIN(COLLECT({Project Plan - start date}, {Project Plan…
