On Tue, 29 Mar 2005 09:26:01 -0600, "Johnny" wrote:

Hello,

I use Excel 2000, and I'd like some help with handling dates. I want to
calculate the number of days between two dates for all the years from
January 1, 2001, to December 31, 2100. The two dates are the day
Daylight Savings Time (DST) starts and the day it ends.

Two facts: (1) DST always starts on the first Sunday in April and
always ends on the last Sunday in October. (2) Although I imagine
Excel knows this, every year in the 21st century that's divisible by 4
(2004, 2008 . . . 2096) is a Leap Year (29 days in February instead of
28). Note, however, that the year 2100 is NOT a Leap Year, which I
imagine Excel also knows.

So, how do I set up the 100 rows so they show the first Sunday in April
in one column and the last Sunday in October in the next so I can
calculate the duration of DST each year? (Tip: It's always 203 days or
210 days.)

Thanks for any help you can offer.

--Johnny
http://barelybad.com


If your list starts in Row 2, then

1st Sunday in April:

=DATE(ROW()+1999,4,8)-WEEKDAY(DATE(ROW()+1999,4,7))

Last Sunday in October:

=DATE(ROW()+1999,11,1)-WEEKDAY(DATE(ROW()+1999,11,7))

And you can copy/drag this down as far as necessary.


If your list starts in other than the 2nd row, just adjust the factor
ROW()+1999 to generate 2001 in the first row.


--ron