In the enumeration used by weekday
function by default, Tuesdays, Thursdays and Saturdays are days 3, 5, and 7 of the week. The solution I give below is not special to these particular days: you can replace {3,5,7}
below with any other selection of days of week.
=A2 + 1 + min(arrayformula(mod({3,5,7} - weekday(A2) - 1, 7)))
This is a formula that would appear in A3, computing the next date based on A2. The starting date at A2 doesn't have to be in the acceptable range itself.
Explanation:
A2 + 1
is tomorrow, the minimal possible value of A3. We want to find out if this value is acceptable; if not, by how much it should be increased.
arrayformula(mod({3,5,7} - weekday(A2) - 1, 7))
find the day intervals between tomorrow and the acceptable days of week. The mod
function is needed to wrap things up correctly: e.g., mod(3 - 6, 7) = mod(-3, 7) = 4
- Finally,
min
determines the smallest of those day intervals, and adds it to the value of tomorrow.
I used two arrayformulas for this purpose. One, in cell C2, to create the list of dates:
=arrayformula(if(row(C2:C)+A2-2 > max(A2:A), , row(C2:C)+A2-2))
It adds the row number minus 2 to the date in A2. If the result is greater than the last date in column A, this date is out of range and is not shown.
Then in column D, vlookup
is used to locate the value for each date. If it cannot find the exact date (raising an error), 0 is used.
=arrayformula(iferror(vlookup(filter(C2:C, len(C2:C)), filter(A2:B, len(A2:A)), 2, false), 0))
The last parameter of vlookup
, "sorted" is set to False in order to force exact match, even though the array of dates is actually sorted.
Best Answer
This can be done with a conditional statement involving
WEEKDAY
Let's say you put 5/7/2015 in A2, and the conditional formula
in A3. (Weekday=6 means Friday, after which two days are skipped). Dragging this down you'll see
and so on.