Calendar Block-Out Dates?

AMCGil

Is there a we a way to block out certain dates from being selected in a request Date Calendar field (example 12/18/2023- 1/3/2024)?

Thank you!

AravindGP

Hi @AMCGil

The validation can only happen when the data is entered directly in the sheet. Since you're using a form, there's a workaround you can do to avoid the dates. Once the data is entered and captured in the sheet, you can use a formula to capture the date in another column. You can have a requested date for the change through the form and have a formula in target effective date as below. This formula will check if the date entered in the requested date field is in the holiday list (hopefully saved in a different sheet) and leave the target effective date as blank to inform someone through automation to set a date. If the date entered falls on a Saturday or Sunday, it can update the target effective date as Monday.

=IF(HAS({Holiday dates range from a separate sheet}, [Requested Date]@row), "", IF(WEEKDAY([ Requested Date]@row) = 1, [Requested Date]@row + 1, IF(WEEKDAY([Requested Date]@row) = 7, [Requested Date]@row + 2, [Requested Date]@row)))

AravindGP

Hi @AMCGil ,

You can filter out dates if you've project settings enabled. To have project settings enabled, your sheet needs to have two date columns. Once you've enabled project settings, you can enter the individual dates from 18th December 2023 to 3rd January 2024 in the holidays list.

To enable project settings, right click on any column to go to "Edit Project Settings"

Enable Dependencies (new columns will be created for duration and predecessors, if it doesn't exist already)

Click on "Edit" under Working Days to add the holidays

AMCGil

@AravindGP, thank you for your reply! I have done as you described, however, I was still able to select 12/31/2023 through my request form. Am I missing steps to implement this solution?

The field being selected in my form is Target Effective date. I have also tried swapping out the start and end date columns in Project Settings- nether was successful

What I'm seeing in the form:

AravindGP

Hi @AMCGil

The validation can only happen when the data is entered directly in the sheet. Since you're using a form, there's a workaround you can do to avoid the dates. Once the data is entered and captured in the sheet, you can use a formula to capture the date in another column. You can have a requested date for the change through the form and have a formula in target effective date as below. This formula will check if the date entered in the requested date field is in the holiday list (hopefully saved in a different sheet) and leave the target effective date as blank to inform someone through automation to set a date. If the date entered falls on a Saturday or Sunday, it can update the target effective date as Monday.

=IF(HAS({Holiday dates range from a separate sheet}, [Requested Date]@row), "", IF(WEEKDAY([ Requested Date]@row) = 1, [Requested Date]@row + 1, IF(WEEKDAY([Requested Date]@row) = 7, [Requested Date]@row + 2, [Requested Date]@row)))

AMCGil

@AravindGP , this is great, thank you!