Newspaper puzzle challenge

[shanjar]

A while ago I asked for help in compiling a formula that would act as a daily check on puzzle numbers that appear in a newspaper.

The daily crossword would appear from Monday to Saturday (but not on Sundays or on Christmas Day)

The formula some genius in the forum came up with was:

=IF((A1)="","",IF(OR(WEEKDAY((A1))=1,TEXT((A1),"d-m")="25-12"),"0",24962+SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE(2006,4,11)&":"&(A1))))<>1),--(TEXT(ROW(INDIRECT(DATE(2006,4,11)&":"&(A1))),"d-m")<>"25-12"))))

In Cell A1 I would input the date.

For today's date, this would produce the answer that the correct crossword should be number 25,720.


Now I need help to revise this formula because the puzzle appears only on the following days: Tues Wed Thurs Fri

It will not appear on Mon, Sat and Sun nor will it appear on Christmas Day.

Thus, next week the puzzle numbers should appear as:

Today (25,720) Sat () Sun () Mon () Tues (25,721)

Wed (25,722) Thu (25,723) Fri (25,724) Sat () Sun () Mon ()

Tues (25,725) Wed (25,726) etc



Anyone up for the challenge?

Many thanks....

 

[ravishankar]

Hi
B1=25720+ ROUND((DATEDIF("09/12/2008",A1,"D"))*4/7,0) where A1 has the date to be checked
Ravi

 

[bridgesm]

Hi sorry this isn't going to be a helpful post, but I can't help wondering why you don't just look at the number that was printed last... and then add 1!

I'm sure there is a reason for not doing this, but well it just got me thinking. I will be having a look at this though... it looks like more fun than actual crosswords.

 

[xld]

Surely, Tue will be 25470, not 25721, because you are counting fewere days in the week, so the total will be less

=IF((A1)="","",IF(OR(WEEKDAY(A1)<3,WEEKDAY(A1)=7,TEXT((A1),"d-m")="25-12"),"0",
24962+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(2006,4,11)&":"&(A1))))={3,4,5,6})*(TEXT(ROW(INDIRECT(DATE(2006,4,11)&":"&(A1))),"d-m")<>"25-12"))))

 

[xld]

If it must continue that number sequence, you could adjust the base number

=IF((A1)="","",IF(OR(WEEKDAY(A1)<3,WEEKDAY(A1)=7,TEXT((A1),"d-m")="25-12"),"0",
25213+SUMPRODUCT((WEEKDAY(ROW(INDIRECT(DATE(2006,4,11)&":"&(A1))))={3,4,5,6})*(TEXT(ROW(INDIRECT(DATE(2006,4,11)&":"&(A1))),"d-m")<>"25-12"))))

 

[shanjar]

XLD, thank you a million times. I spent hours trying to find a way around this dilemma and you reeled it off in minutes. You're an absolute star.