Looking for specific date formula
Posted by Beginner Bob on January 07, 2002 4:02 PM
I have several worksheets in my workbook where each sheet corresponds to a year (1999-2010). I'm looking to put a formula in cells B8:B59 that calls the 1st Tuesday of the year (from B8) to the last Tuesday of the year (to B59), hence the 52 rows. The year will be specified in cell B3, but I'm not sure of the function sequence (ie: weeknum, weekday functions) to use. Can someone help please? Thanks.
Posted by Tyrrel on January 07, 2002 4:59 PM
Format B3 as date ("yyy") and input the 1st day of the year (eg 01-01-2000)
Put the following formula in B8 (formatted as date) :-
=B3-1+IF(3<WEEKDAY(B3-1),7-WEEKDAY(B3-1)+3,3-WEEKDAY(B3-1))
In B9 put =B8+7 and fill down to B59
Posted by Tyrrel on January 07, 2002 5:00 PM
Didn't post properly - look at the Comments box.
Posted by Jacob on January 07, 2002 5:11 PM
Hi
There is probably an easier way to do this but it will work
In B8 put
=IF(WEEKDAY(DATE(2001,1,1))=3,DATE(2001,1,1),IF(WEEKDAY(DATE(2001,1,2))=3,DATE(2001,1,2),IF(WEEKDAY(DATE(2001,1,3))=3,DATE(2001,1,3),IF(WEEKDAY(DATE(2001,1,4))=3,DATE(2001,1,4),IF(WEEKDAY(DATE(2001,1,5))=3,DATE(2001,1,5),IF(WEEKDAY(DATE(2001,1,6))=3,DATE(2001,1,6),DATE(2001,1,7)))))))
in B9 put
=B8 + 7
fill down
HTH
Jacob
