Is there a CHOOSE function or something equivalent in Smartsheet?
Hi,
I am build a process in Smartsheet to calculate the number of workdays between two calendar dates, including the start and end date, this is pretty strait forward. The issue that I running into is removing company holiday dates. Currently I am manually creating a list of holidays and then on holidays that are not specific dates (ie. Memorial Day, Thanksgiving, etc.) I have to open a calendar to find out what that date is for that particular year.
In experimenting I found I use the CHOOSE function in Excel to reference a cell containing the year and use it in a formula to calculate the date automatically, for example:
=DATE([YEAR]1,11,1)+21+CHOOSE(WEEKDAY(DATE([YEAR]1,11,1)),4,3,2,1,0,6,5)
Used for calculating the date for Thanksgiving, [YEAR]1 is a dropdown select with a list of years.
Please let me know if anyone has a solution for this and/or a working example.
Thank you so much!
Answers
-
Try replacing the CHOOSE with this:
ABS(MOD(WEEKDAY(Date@row), 7) - 5)
With 5 being Thursday.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!