Not so pretty IF-function (works) but needs makeover

HOFF

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 IF-formula 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

Paul Newcome

What is the reason for capturing all of them instead of just the beginning and the end?

HOFF

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)

Paul Newcome

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.