Can I use a date - formula field to force users to input only the current date or a past one, no future dates?

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered
I have date fields that need to be populated with actual dates of task completion. I do not want the user to put in a future date.
Photo of Mike


  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Scott


  • 334 Points 250 badge 2x thumb

To do this, we'll need two fields.  (Or three with an optional field)

Formula - Text: "Task Completed"
Date: "Task Completed Date"
Formula - Date: "Task Completed Date*" (OPTIONAL)

The formula "Task Completed" will house the logic for the date rejection/acceptance, while "Task Completed Date" will be the field where the users will enter their date.  Formula "Task Completed Date*" will be the same as "Task Completed" without any text, so it only shows dates. (OPTIONAL).

For the "Task Completed" formula, we will use something like the following...
If([Task Completed Date]>Today(),"Enter a date that has already happened!",[Task Completed Date])

This means, if the user enters a date in "Task Completed Date" then it will only show dates that have already passed in the "Task Completed" formula.

Additionally, you can get rid of the extra verbiage in the "Task Completed" formula, by making a date formula "Task Completed Date*" with the following...
If([Task Completed Date]>Today(),null,[Task Completed Date])


If([Task Completed]="Enter a date that has already happened!",[Task Completed Date])

It's up to you which version of the optional field you use, however the second version is dependent on the first formula we created.  

The reason I believe using a Text formula is better, is because there will be an error message to inform the user that future dates aren't allowed.  Without it, they may enter a date and not realize that it is being rejected by the system.  

Hopefully this helps you out a little bit!  I know its not *exactly* what you were looking for, but it is a clever work-around.  There's an even more complicated work-around using dandiebolt's image onload method, but it's likely not needed here.

Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
Using the image onlaod technique you can do precisely what you want. QuickBase uses the jQueryUI library for their datepicker and it comes with a lot of options to configure what dates can be selected and other behavior:

jQueryUI Datepicker Docs

This code will prevent future dates from being selected:

Pastie Database

You could still manually enter a date in the future but that too could be change with a little custom JavaScript. The jQueryUI datepicker can be configured for example to only select weekdates, particular days of the week, date ranges or a list of specific dates.