software

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: www.excelforum.com/member.php...oamp;userid=33278
View this thread: www.excelforum.com/showthread...hreadid=531043With 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,quot;No paperquot;,B2-INT((B2-38809)/7)-34814)

--
HTH

Sandy
In Perth, the ancient capital of Scotland

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