Determining volume/number.

[Jim Smith]

I'd like to construct a macro that can generate the proper
volume number and number for a publication. Ideally, the user would
enter the year in a cell and run a macro that would generate the
year's volume/number on another worksheet.
The publication has two sets of volumes/numbers. One for the
Monday through Saturday (daily) edition and one for the Sunday paper.
The anniversary date for the daily paper is 1/26/1880. The anniversary
date for the daily paper is 9/13/1981.
The volume refers to the number of years published. The number
refers to the days thus far in that volume that have been printed.
So, for example, the Sunday proper volume/number on 2/19/06
would be Volume 26 No. 23.
For the daily, the volume/number on 2/21/06 would be Volume
127 No. 23.
On Jan. 26, 2007, the Volume for the daily paper would be 128
and the No. would be 1.
On Sept. 13, 2006, the Volume for the Sunday paper would be 27
and the No. would be 1.
I would also like to add some flexibility to the macro. There
are certain holidays the publication does not publish. I would like to
have the option of adding certain dates in a column that would give an
accurate count.
For example in 2005, the publication did not publish 12/25, so
I would have entered that date to get the proper number for Sunday.
I am using Excel 2000 on XP and NT4.
Thanks for any help.

 

[Gary''s Student]

Not clear:


If volume is number of years, then why 127??

 

[Jim Smith]

In 1880, they started with Volume 1, not Volume 0.

 

[Gary''s Student]

I am sorry Jim, I am still off by one year:

Sunday Paper

In A1 put 9/13/1981
in B1 put 1
in C1 put 1

In A2 thru C2 put:
=A1+7 =DATEDIF($A$1,A2,"y")+1 =IF(B1=B2,C1+1,1)
Then copy A2 thru C2 down to row#2500

The table appears to track to your requirements. The volume bumps on the
aniversery date and the number re-sets to one when the volume bumps.

But if you look at row#1276 you will see 2/19/2006 25 23

My volume is still off by one.


Perhaps 9/13/1981 should be Volume 2 number 1???

 

[Jim Smith]

Thanks very much! That gets me on the right path. I appreciate your
help.

 

[Jim Smith]

I'm having trouble getting the pre-1900 anniversary date to calculate.
Is there a workaround for this? Or another way to got at it? Thanks.

 

[Roger Govier]

Hi Jim

John Walkenbach has an add-in for dealing with pre-1900 dates
http://www.j-walk.com/ss/excel/usertips/tip028.htm

 

[Jim Smith]

I've tried it with the formulas posted but still get the dreaded
#VALUE! error.