Formula applys on a biweekly Thursday in all dates 2017

[lynnsong986]

Hello,

I need help with a formula that does:

I have in col A all calendar dates in 2017, in col B I need a formula that grabs a number (say $10) from another sheet only if the date is a Thursday on a biweekly basis.

for example:
Tuesday 24-Jan-17
Wednesday25-Jan-17
Thursday 26-Jan-17 $10.00
Friday 27-Jan-17
Saturday 28-Jan-17
Sunday 29-Jan-17
Monday 30-Jan-17
Tuesday 31-Jan-17
Wednesday01-Feb-17
Thursday 02-Feb-17
Friday 03-Feb-17
Saturday 04-Feb-17
Sunday 05-Feb-17
Monday 06-Feb-17
Tuesday 07-Feb-17
Wednesday08-Feb-17
Thursday 09-Feb-17 $10.00
Friday 10-Feb-17
Saturday 11-Feb-17
Sunday 12-Feb-17
Monday 13-Feb-17
Tuesday 14-Feb-17
Wednesday15-Feb-17
Thursday 16-Feb-17

I'm ok with the "Thursday" part, I use =if(text(A2,"dddd")="Thursday"...but I don't know how to get the biweekly one work.

thanks so much!
Lynn

[Trevor_S]

Can you fix it based on the first Thursday? Say Thurs 26/01/2017 is in cell A3. The beginning of your "if" statement for row 4 would then be:
=if((A4-A$3)/14=int((A4-A$3)/14),...
What the formula does is check whether the difference between the current and initial date is a multiple of 14 days.

[lynnsong986]

thanks for your help Trevor. Joe4 solved my problem by using iseven(weeknum(A1)), which works perfectly!

[Trevor_S]

That's good, I didn't know about weeknum - that's a much better way to do it!

[Aardigspook]

As an aside, an easier way to check for a Thursday is to use Weekday:

Please Login or Register  to view this content.

the '1' after the cell reference sets Sunday as the first day of the week, so day 5 is Thursday.