Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Brainteaser about Days Between Dates
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 |
#2
|
|||
|
|||
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) -- Regards, Peo Sjoblom "Johnny" wrote in message .. . 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 |
#3
|
|||
|
|||
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 -- Dana DeLouis Win XP & Office 2003 "Johnny" wrote in message .. . 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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
Thank you all very much for your help.
It'd've taken me a lomg time to arrive at just one of these methods. "Johnny" wrote in message .. . 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 |
#6
|
|||
|
|||
On Tue, 29 Mar 2005 09:26:01 -0600, "Johnny" wrote:
Two facts: (1) DST always starts on the first Sunday in April and (2) ...every year in the 21st century that's divisible by 4 (2004, 2008 . . .. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Determining the number of specific days between two dates in Excel | Excel Worksheet Functions | |||
formula to calculate # of days between dates, excluding holidays | Excel Discussion (Misc queries) | |||
Is there a way to calculate business working days between dates i. | Excel Worksheet Functions | |||
how do I calculate the days between dates? | Excel Discussion (Misc queries) | |||
Subracting Dates to come up with the # of days between them | Excel Worksheet Functions |