Calendar Block-Out Dates?
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!
Best Answer
-
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)))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers
-
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
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
@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:
-
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)))
Thanks,
Aravind GP| Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
-
@AravindGP , this is great, thank you!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives