If stmt and date range for leap year

[blue blue]

I have two cells in EXCEL, one with a beginning date and one with an ending date. I need to get a value of "0" when the range between the dates includes 2/29/08. If 2/29/08 is not in the range I need a value of 1. I am subtracting the beginning date from the ending to get the number of days, then adding the 1,or 0 because I do not want to include any leap days. I have tried many different IF statements, including nested stmts, without success. Thanks for any help.

 

[Blade Hunter]

I have started off but have been given an urgent task for work. Hopefuly someone can pick up where I left off.

This will determine if a year (in A1) is a leap year.

=IF(MONTH(DATE(YEAR(A1),2,28)+1) = 2,"This is a leap year","Not a leap year")

 

[blue blue]

I think I need to explain further. The beginning date might be 1/21/08 and the ending date might be 1/20/09. I want the number of days from 1/21 through 1/20 of the following year to calculate as 365, even though 2008 is a leap year and the number of days from 1/21/08 thru 1/20/09 is actually 366. However, if the beginning date is 3/1/08 or later I don't have to worry about 2/29 adding a day. So, I need a way to determine if 2/29 falls between the beginning and ending dates.

 

[AlphaFrog]

If you are testing if the date range includes the specific date 2/29/2008

A1 = start date
B1 = end date

=IF(AND(B1>DATE(2008,2,29),A1< DATE(2008,2,29)),0,1)

 

[AlphaFrog]

A1 = start date
B1 = end date

=B1-A1-IF(AND(B1>DATE(2008,2,29), A1< DATE(2008,2,29)), 1,0)

 

[Blade Hunter]

Do you mean something like this?

Cell C1 is the formula, Cell D1 is the straight count of days for comparison.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 75px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right">8/02/2008</TD><TD style="TEXT-ALIGN: right">1/01/2009</TD><TD style="TEXT-ALIGN: right">327</TD><TD style="TEXT-ALIGN: right">328</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>C1</TD><TD>=ABS((DATE(YEAR(NOW()),MONTH(B1),DAY(B1))-DATE(YEAR(NOW()),MONTH(A1),DAY(A1)))+ ((YEAR(B1)-YEAR(A1))*365))</TD></TR><TR><TD>D1</TD><TD>=B1-A1</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
Excel tables to the web - Excel Jeanie Html 4


It works by calculating the days between the start and end dates for the current year then absolutes it (in case your start date is after your end date once we make them the same year) Then it adds the number of years difference * 365.

This will come unstuck where the current year is a leap year and 29th feb for that year is in your range however.

You can counter this by summing with a modified version of my original formula like so:

=SUM(ABS((DATE(YEAR(NOW()),MONTH(B1),DAY(B1))-DATE(YEAR(NOW()),MONTH(A1),DAY(A1)))+ ((YEAR(B1)-YEAR(A1))*365)),IF(MONTH(DATE(YEAR(B1),2,28)+1) = 2,-1,0))

 

[blue blue]

Thanks so much guys. Worked great today at work. OK, Blade Hunter, I'll have to take some time to figure out your last approach (way over my head.)

 

[Blade Hunter]

I can explain it if you like:

=SUM(ABS((DATE(YEAR(NOW()),MONTH(B1),DAY(B1))-DATE(YEAR(NOW()),MONTH(A1),DAY(A1)))+ ((YEAR(B1)-YEAR(A1))*365)),IF(MONTH(DATE(YEAR(B1),2,28)+1) = 2,-1,0))

Look at the first part of the sum:

ABS((DATE(YEAR(NOW()),MONTH(B1),DAY(B1))-DATE(YEAR(NOW()),MONTH(A1),DAY(A1)))+ ((YEAR(B1)-YEAR(A1))*365))

This bit:
DATE(YEAR(NOW()),MONTH(B1),DAY(B1)) returns the END date but with the current year instead of the year you specified

This bit:
DATE(YEAR(NOW()),MONTH(A1),DAY(A1))) returns the START date but with the current year instead of the year you specified

The modified Start date is subtracted from the modified end date. This will tell us how many days between the two dates but for THIS year instead of the years specified.

Being that it is quite plausible for this to return a negative (ie start date of 11/11/2009 and end date of 30/1/2010 when both have the year changed to the current year it would return -80, 80 is the correct figure) so we absolute this number, absolute will simply chop the negative sign from the front.

Now we know the days between the two dates but we need to add 365 times the number of years between the two, this is done here:

+ ((YEAR(B1)-YEAR(A1))*365))

As said, this comes unstuck if your range includes a leap year that is the CURRENT year ie if it were 2008 now.

So we need to work out if we should subtract one or not, this is done with the second part of the sum formula:

IF(MONTH(DATE(YEAR(B1),2,28)+1) = 2,-1,0)
Basically it is saying IF we take the date of 28 feb and the year of the end date and add one to it, is it still Feb, if it is then we know it is a leap year and we need to subtract 1 from the final number (hence the -1) if it isn't then we add nothing (hence the 0)

Sounds convoluted but it's fairly basic when you break it down.

Hope that helps

Dan<!-- / message --><!-- sig -->

 

[blue blue]

Thank you, Dan. I see how it works, now. Your formula would work for any leap year, not just 2008. Great! Thanks for the detailed tutorial. Helped a lot.