Is it possible for my form to add a calendar to select dates?

Bernard Oakley

This form is for employee perdiem. I want them to select dates from a calendar, Monday through Sunday. Which will show the dates they are requesting perdiem spread sheet. Right now it only shows Monday - Sunday, but having the dates will help on my end. Where the days of the week are, I would like the date.

Bassam Khalil

Hi @Bernard Oakley

Hope you are fine, i think you can see the day of the week with the date of that day by using the following format in the column of selected perdiem day from date format.

Genevieve P.

Hi @Bernard Oakley

Bassam is correct in that if you want users to select what day of the week and have it displayed in your sheet along with a date, you will need the column to be a date-type of column.

However it is possible to build formulas and helper columns in the sheet to populate once the user has submitted the form.

You could set up 7 new Date Columns, one for each day of the week. Then, in the MONDAY column, you could look to see if your mutli-select Day cell for this row HAS the word "Monday".

IF(HAS([Days MultiSelect]@row, "Monday"

You will then want to check the date in the Date Column (third one in your screen capture, I assume this is the week you want to be looking at?) and find out which day of the week that date is.

WEEKDAY([Date from Form]@row)

Then based on that day of the week, you can either add or subtract week days to return Monday's date for that week.

IF(WEEKDAY([Date from Form]@row) = 1), [Date from Form]@row + 1

Here is a full example formula for MONDAY:

=IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 1), [Date from Form]@row + 1, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 2), [Date from Form]@row, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 3), [Date from Form]@row - 1, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 4), [Date from Form]@row - 2, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 4), [Date from Form]@row - 3, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 5), [Date from Form]@row - 4, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 6), [Date from Form]@row - 5, IF(AND(HAS([Days MultiSelect]@row, "Monday"), WEEKDAY([Date from Form]@row) = 7), [Date from Form]@row - 6))))))))

Then for TUESDAY, you would change out what word you're looking for

=IF(AND(HAS([Days MultiSelect]@row, "Tuesday")

And how many days you're adding/subtracting, depending on the other date column:

=IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 1), [Date from Form]@row + 2, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 2), [Date from Form]@row + 1, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 3), [Date from Form]@row, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 4), [Date from Form]@row - 1, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 4), [Date from Form]@row - 2, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 5), [Date from Form]@row - 3, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 6), [Date from Form]@row - 4, IF(AND(HAS([Days MultiSelect]@row, "Tuesday"), WEEKDAY([Date from Form]@row) = 7), [Date from Form]@row - 5))))))))

Is that what you were looking to do?

Cheers!

Genevieve