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?

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.
Help Article Resources
Categories
Check out the Formula Handbook template!