Is there a CHOOSE function or something equivalent in Smartsheet?

cthrasher97141

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!

Paul Newcome

Try replacing the CHOOSE with this:

ABS(MOD(WEEKDAY(Date@row), 7) - 5)

With 5 being Thursday.