formula to verify dates

I have a form running off a sheet containing separate date columns for start date and end date. Users of the form are accidentally selecting an end date that is before the start date. Is there a way to formulate an error message for when that happens?

And/or... can someone help me formulate on the sheet for when the end date precedes the start date? I have this a starting point, but it does not work.

=IF([End Date]@row < [Start Date]@row, "Red")

Thank you in advance,

Rebecca Panaccione

Find more posts tagged with

Accepted answers

All comments

Rebecca Panaccione

I did create a helper column.

Alejandra

Hi @Rebecca Panaccione,

There currently isn't a way to display a customized error message in order to control data entry. When you have a moment, submit a Product Enhancement Request to let our Product team know that you'd like to have this functionality in Smartsheet.

Also, I tested your formula and it worked for me —it returned the word "Red" when the end date is smaller than the start date. If you want to return a red symbol, you might need to change the column type.

Rebecca Panaccione

Well, something is working, but I'm not sure how to fix this. Here is a screenshot of my sheet. I added a helper column to test the validity of the end date, but I'm clearly not doing something right. :(

I entered a test with invalid end date (top row) and I need it to indicate that the end date is either the same as or greater than the start date. Or, alert me that the end date is NOT the same or greater than the start date.

The formula I used =IF([End Date]@row < [Start Date]@row, "Red") is returning a true value, but it is not true. In this case, my End date is smaller than my Start Date and show be false.

Can you help?

Rebecca

Alejandra

@Rebecca Panaccione In the formula, you specified to return "Red" if it's true that the end date is smaller than the start date —which it is. However, the value returned should be "Red" (or a red harvey ball), not the word "true".

Try using this formula and change the Date Validation column type to Symbol > Status > RYB Ball:

=IF([End Date]@row < [Start Date]@row, "Red", IF([End Date]@row >= [Start Date]@row, "Green"))

If that works for you, then you can create an alert that uses the Date Validation column in the condition —like this:

Screen Shot 2020-02-20 at 7.34.25 AM.png

Rebecca Panaccione

Alejandra,

Thank you, thank you, thank you. This is a beautiful thing! It works perfectly and the alert automation does exactly what I need.

Rebecca

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.