Brainteaser about Days Between Dates

[Johnny]

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

 

[Peo Sjoblom]

First Sunday in April is

=DATE(A1,4,1)+7-WEEKDAY(DATE(A1,4,1)-1)

where A1 holds the year


Last Sunday in October is

=DATE(A1,11,1)-WEEKDAY(DATE(A1,11,1)-1)

 

[Dana DeLouis]

If it's just the duration, them perhaps something like this:
Cell A1 holds the Year:

=IF(OR(WEEKDAY(DATE(A1,4,1))={2,3,4}),203,210)

HTH

 

[Ron Rosenfeld]

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

 

[Johnny]

Thank you all very much for your help.

It'd've taken me a lomg time to arrive at just one of these methods.

 

[Myrna Larson]

.. 2096) is a Leap Year

Yes, but your 2nd point isn't relevant since the time span of interest doesn't
include the end of February. As you say, the answer is either 203 or 210 days,
which can be determined by the day of the week on which April 1 falls.

If April 1 is a Monday, Tuesday, or Wednesday, DST is in effect for 203 days,
otherwise 210. So to answer your specific question, with the year (i.e. 2010
-- not a full date), in A1

=IF(WEEKDAY(DATE(A1,4,1),2)<4,203,210)

or

=203+7*(WEEKDAY(DATE(A1,4,1),2)>3)

PS: I happened to choose April 1 as the date to check, but if you know the day
of the week for March 1, you can calculate the day of the week for any date
later in that year.