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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!