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.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!