Forms: how to make dates static

Pamela Wagner

We have a form that has a date field for "week ending". We'd like to be able to only have the dates that are for the actual end date (Saturday) to be available for use. Right now since it's a date field it pulls up the calendar and they can choose any date. Other than going to a drop down and putting in the next 56 Saturdays as options, is there a way to tell the calendar to only allow Saturdays as the option?

Bassam Khalil

Hi @Pamela Wagner 

Hope you are fine, could you try the following:

1- i blank sheet create date column type and in the first cell select the nex Saturday date letus say it will be 30-Jan-2021

2- to calculate the next Saturday date in the cell bellow cell1 the formula will be ( =[Saturday Date]1 + 7 ) the result will be ( 6-Feb-2021 )

3- now select cell1 & 2 and as you do in excel to fill drag down you will have all Saturday date even if you want it for next 5 year.

4- copy all Saturday date you get and but those value on a single dropdown column and use it in your form to make the users select only Saturday date.

Neil Watson

@Pamela Wagner, adding to the suggestion above there are a few third-party offerings that can dynamically populate drop-down lists from a source datasheet. So if your datasheet is set up to (using your example above) to show the dates for Saturdays in the future, via a formula then you will not have to update the drop-down list.

Pamela Wagner

Thanks Bassam.M Khalil, this is what I was hoping not to do but it's certainly an option!