Formulate LEAP yr to be 365 days in excel

**forest06** · 05-11-2012, 07:58 AM

I am suppose to calculate the days between 2 dates. However, leap yr would be 366 days but i have to formulate it so that no matter whether its leap yr or not, no. of days would be 365. So I would need to exclude 29 feb from every leap yr.

I've tried using the formula I found in another forum:
=SUMPRODUCT(--(TEXT(ROW(INDIRECT(F4+1&":"&F5)),"dd-mmm")<>"29-feb"))
but when I input 01/01/2007 and 31/12/2007, the no. of days reflected was 364 days.
and when I input 01/01/2008 and 31/12/2008, the no. of days reflected was 364 days too. I have no idea what went wrong.

Anyone have any advise for me? Or any formula to recommend???
Need lots of help for Excel!!! Thanks in advance!

**Bob Phillips** · 05-11-2012, 08:24 AM

You don't add 1 to the start date

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(F4&":"&F5)),"dd-mmm")<>"29-feb"))

You need to be inclusive.

**forest06** · 05-11-2012, 03:07 PM

Originally Posted by Bob Phillips

You don't add 1 to the start date

=SUMPRODUCT(--(TEXT(ROW(INDIRECT(F4&":"&F5)),"dd-mmm")<>"29-feb"))

You need to be inclusive.

thanks sooooo much! issue solved. but is it possible to explain how does the formula works? like the "text", "row" etc.

**Bob Phillips** · 05-12-2012, 05:08 AM

The F4&":"F5 starts by creating a range string based upon the start (1st Jan 2007) and end dates (31st Jan 2007). In your example it equates to "39083:39447"

INDIRECTing this string creates a reference to that range, and ROW(INDIRECT(F4&":"F5)) creates an array of the row numbers within that range, {39083;39084;39085;39086;39087;39088;...;39447}. This has the effect of creating an array of all of the dates between start date and end date, which the formula can now check.

Using the TEXT function on this, TEXT(ROW(INDIRECT(F4&":"F5)),"dd-mmm") creates an array of those dates in this format {"01-Jan";"02-Jan";"03-Jan";"04-Jan";"05-Jan";"06-Jan";"07-Jan";"08-Jan";...;"31-Dec"}

This array is then compared against the string for 29-Feb, TEXT(ROW(INDIRECT(F4&":"F5)),"dd-mmm")="29-feb", which returns an array of TRUE/FALSE for all of those dates. Every value will be TRUE, except for the one of 29th Feb (if there is a 29th Feb).

The last but one step coerces this array of TRUE/FALSE to an array of 1/0 --(TEXT(ROW(INDIRECT(F4&":"F5)),"dd-mmm")), and finally SUMPRODUCT adds up all of those 1 and 0's to calculate the number of days between those two dates that are not 29th Feb.