Not so pretty IFfunction (works) but needs makeover
Keeping track on vacation time I need to convert dates to weeks and then again to an overview with checkboxes.
In order to get a list of weeks [c_weeks_listed] (later in use with FIND) I have come up with the following IFformula which works but will become too complex if expanded further.
Any ideas for a more powerful formula (Lookup? or ??)
=IF([c_week diff]@row < 1; [c_vacation week, start]@row; IF([c_week diff]@row = 1; [c_vacation week, start]@row + ", " + ([c_vacation week, start]@row + 1); IF([c_week diff]@row = 2; [c_vacation week, start]@row + ", " + ([c_vacation week, start]@row + 1) + ", " + ([c_vacation week, start]@row + 2); IF([c_week diff]@row = 3; [c_vacation week, start]@row + ", " + ([c_vacation week, start]@row + 1) + ", " + ([c_vacation week, start]@row + 2) + ", " + ([c_vacation week, start]@row + 3)))))
Best
Hoff
Answers

What is the reason for capturing all of them instead of just the beginning and the end?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!

I need all weeks so I can display when people are on a holiday and check if too many (or few) are keeping the department afloat during peak vacation (uge is week)

Ok. We may be able adjust the formula in the checkboxes so that you don't need the text string. Basically we check the first box based on the start, check the last box based on the end, then check the middle boxes based on being greater than the start and less than the end (if all of that makes sense).
If you insert a "helper row" you can enter the week numbers cross this row and use cell references which will make scaling much easier. To get the numbers into the helper row, you will need to enter them as text.
="1"
="2"
="3"
So on and so forth.
Then the formula to check the boxes (starting in [Uge 1] and dragfilling down and right) would look something like this:
=IF(AND(VALUE([Uge 1]$1) >= $[c_vacation week, start]@row, VALUE([Uge 1]$1) <= $[c_vacation week, end]@row), 1)
Using this method removes the need for the [c_week diff] and [c_weeks listed] columns and will cover as many weeks as you need without having to adjust the formula to account for 1 week vs 2 weeks vs 3 weeks, etc.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am  12:45pm!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.8K Get Help
 376 Global Discussions
 207 Industry Talk
 440 Announcements
 4.5K Ideas & Feature Requests
 139 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 284 Events
 33 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!