Date Logic in Form

dpm1028

In forms it is not uncommon to have a Start and End date for any reason (mine is for PTO). It would be very helpful if there could be some logic added to Date fields to compare with other Date fields. In my case I need to add logic that prevents a user from entering an End Date that is prior to the Start Date. Today this does not exist.

John Bray

I could use another date format with the day in it, so only Tuesdays could be selected as an example.

It appears that this is not possible in forms now.

ker9

In the meantime, you could use a helper column with a formula that returns the day number:

=DAY(DATE@row)

Where DATE is the column name that contains your date.

Results: 1=Sunday, 2=Monday, 3=Tuesday, etc.

John Bray

I was attempting to utilize a form and sheet to track meeting requests on a set day. I adjusted the Timeline display to Months, Weeks, and Start Day to Tuesday, this will work for what I need currently. Though not as elegant as being able to select/restrict set days/dates directly utilizing the calendar on the form.

Thank You for the Suggestion!

_jacob

Currently, the DATE field allows us to enter a specific date -- however this date is almost impossible to preset to the current date without javascript or bookmark workarounds.

Idea 1: An option to easily set the prefill of DATE to (today).

Idea 2: Allow the use of dropdown boxes to store time in the DATE formula. This is a very popular future in SharePoint and makes migrating to Smartsheets slightly more annoying than it should be.

Manually entering time information formulates information incorrectly, it's also more prone to user error.

Thanks,

Jacob

Violeta_1

We have a form that asks users to select from a drop down their preferred day and time and their back up day and time. Right now I cannot prevent users from selecting the same day/time as their preferred and back up.

It would be nice if there was an advanced logic that we could add to the form that would hide the value in the drop down list that has already been selected by the user in another field. For example, in our form it would prevent users from selecting the value they already selected from their preferred day and time to their back up day and time in their form submission.

jacquedale

allow for an easy way to setup booking feature within the form. My field teams allow contractors to scheduled desired dates/times for inspections however I am having to build a complex work around instead of a simple booking addition that would eliminate options as they are selected.

jsquaredz

A typical scenario is having a start and end date. There are times where we want to limit the duration between the two. Would be great to have a validation that can be setup that will calculate against another date field and validate against a max or min difference. Also a validation for start date relative to the current date. so that a start date less than X days from submission is not allowed.

Trish Dillon

i have a form that needs to have dates checked and validated on the form that the date chosen is not a weekend or holiday. 

would be nice to have criteria to allow or validate that is customized

OORF

In a Smartsheet form, having the ability to have a list of dates, but be able to apply logic to only show future dates, or dates within the next x time period.

Karinola84

To restrict users to enter an End Date in the past.

AEForrest

https://community.smartsheet.com/discussion/comment/367279#Comment_367279

@jsquaredz There is a way to (kind of) prevent / rectify form submission when the start date chosen is less than X days in the future (etc.).

In the left pane of the Form Editor, drag over a 'Heading/Description' element to under or over your Start Date field. Enter a title and some guidance in red font (see below example).

Then, click on your Start Date field and mark it as 'Required'. Then select 'Logic' in the right pane. Add a rule that will show the new Heading/Description form element if the Start Date is within X number of days. Add another rule that will show a another field (I used my "Time Slot" field) if the Start Date is NOT within X number of days.

When you test out your Form you will see that the Heading/Description field and the mandatory completion field you chose do not appear on the Form until a Start Date is entered. As the Start Date field is marked as Required, this prevents the user from submitting the form until an acceptable date (and then your other chosen field) has been entered:

With an unacceptable date entered you would see this:

With an acceptable date entered you would see this:

A drawback is that the form can still be submitted with an unacceptable date, BUT 1) the warning would have to be ignored, 2) the other field couldn't have been completed as it wouldn't have appeared. So you could then set up an Automation on the Sheet so that as soon as a new entry is received, if X field is blank (which it would be in the case of an unacceptable date), an update request is sent to the submitter (oh, don't forget to have a required field on the Form for the Submitter's email address, and ensure it is restricted to email addresses only) asking them to select an acceptable date and to complete the other required field (that didn't appear on the Form initially).

You could also have a column on the Sheet that will only tick if all the required fields are not blank. Then if you needed to, you could either leave it at that, or set up an Automation on the Sheet to move rows onto another Sheet of 'Approved Entries'.

Thoughts?

Megan_ClavieParker

When building a Form, allow custom validation on Date fields.

I have a form with an "Effective Date" field. I need the option to limit the range of dates that can be entered. For this particular form, I need to select a specific range to allow (4/1/24-9/30/24). For other forms, similar date validation such as "must not be in the past" would be useful. My current "workaround" is help text stating that the date must be in that range, but it does not prevent people entering requests with unallowable dates.