Date Logic in Form

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.

Sign in to vote!

Find more posts tagged with

Forms

Formulas

On Wishlist

Comments

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.

Amna Ahmed

In a smartsheet form we have for a service request process, we need the ability to select a future date only . Currently it is not allowed which allow users to requests a date in past - Can you please allow the ability to choose a future date only when the purpose of the FORM is for a service requests or resource request .

The way it should work is:

User picks the service they need - and the date they need it done by

The date field should NOT allow them to pick past dates

Ideally build logic that respects the prupsoe e.g. If it is SLA based form and if they choose a previous field "Impact" value as high which has 3 days SLA form autosuggest date respecting the business dates and auto poulating the dates

degerax0

When using a date column in forms, it would be great to have the ability to restrict it to a specified set of dates. And it would also be great to have the added option to automatically update the specified set of dates by, for example, removing dates once they are in the past. I have several different workarounds for the former that I have used, such as listing the dates as text values in a dropdown list or using a different form for each of the dates and hiding the field in the form with a default value. However, it takes more time putting those workarounds in place and adds further complexity to the underlying sheet.

divelocker

Hi team,

Would it be possible to make pop-up calendar in a booking form to display certain days as not-available for booking - a bit like with airline website calendars showing on which specific days they do have flights to selected destination.

We want our end-users to book equipment 2 working days in advance. Some do not follow the rule and we then have to manually reject their request which might happen some time after they've created the booking.

A calendar which displays 'available only' days would help us immensely in the automation of the process.

Below current view of our booking form - even the weekends are "available" for booking…

Thanks!

Quick Links

Trending ideas

Report Widget Capability - Drag & Drop to create Report Widget in Dashboard from Workspace Panel
Hi, I was creating new dashboard and thought it'll be so nice to have easy drag and drop Report widget capability from Workspace Panel since I always group set of reports under one folder (for ease of finding them what goes on the dashboard). I think this would be great time saver especially if you have similar name…
Resource Management Plan Settings in Control Center - add Client and Project Code fields
In the Resource Management Plan Settings in Control Center, there is no way to link the Client and Project Code fields even though they are not Custom fields and part of the first 3 fields you enter when creating a new project. I can't select them in the Custom Resource Management Information Fields because they are not…
Asset Sharing should default to View, not Edit
Since the license model has changed, the option to Share should default to VIEW, not EDIT. The View option allows less-experienced users to share that way by default, and share as Edit only when needed. This allows for less cleanup during true-up. This also prevents users from adding provisional licenses unknowingly.