Checking crossword numbers

[shanjar]

I want to write a formula that works out what the crossword number in a daily newspaper should be.

For example, say the crossword number for today is 4000 and today is Friday.

What formula would return tomorrow's (Saturday's) number (4001) correctly?

And - most crucially - what formula would return Monday' correct crossword number - 4002 - as there is no daily newspaper on a Sunday?

Help very gratefully received. I'm going round in circles...

[Sandy Mann]

With the date in B2 then

=B2-INT((B2-38809)/7)-34814

seems to return the right number and if the date is a Sunday it returns the
previous day's (ie Saturday) date so if your list misses out Sundays the
formula will do so also.

If you have a complete list of dates then use:

=IF(WEEKDAY(B2,1)=1,"No paper",B2-INT((B2-38809)/7)-34814)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

[email protected]
[email protected] with @tiscali.co.uk


"shanjar" <[email protected]> wrote in
message news:[email protected]...
>
> I want to write a formula that works out what the crossword number in a
> daily newspaper should be.
>
> For example, say the crossword number for today is 4000 and today is
> Friday.
>
> What formula would return tomorrow's (Saturday's) number (4001)
> correctly?
>
> And - most crucially - what formula would return Monday' correct
> crossword number - 4002 - as there is no daily newspaper on a Sunday?
>
> Help very gratefully received. I'm going round in circles...
>
>
> --
> shanjar
> ------------------------------------------------------------------------
> shanjar's Profile:
> http://www.excelforum.com/member.php...o&userid=33278
> View this thread: http://www.excelforum.com/showthread...hreadid=531043
>