Formula that calculates leap and regular year days

[ehoward84]

Is there a formula that separates leap year and regular year days between two dates? For instance, from 1/1/2011 to 5/1/2012. If you include the last day, then there are 365 regular year days and 122 leap year days. If you do not include the last day, then there are 365 regular year days and 121 leap year days. Thanks for the help in advance

 

[Steve_R]

Between 1/1/2011 and 5/1/2012 there is no Feb 29. I don't understand 122 leap year days in the period. However, you can ascertain whether a year is a leap year. One way is to use the Date function. Date(year to check,2,29). If the result of that is in March, it's not a leap year.




Sheet1

*	A	B
1	1/01/2013	*
2	1/01/2014	*
3	1/01/2015	*
4	1/01/2016	Leap Year
5	1/01/2017	*
6	1/01/2018	*
7	1/01/2019	*
8	1/01/2020	Leap Year
9	1/01/2021	*
10	1/01/2022	*
11	1/01/2023	*
12	1/01/2024	Leap Year

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:68px;"><col style="width:115px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =IF(MONTH(DATE(YEAR(A1),2,29))=2,"Leap Year","") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[ehoward84]

Thanks for the reply Steve. Since 2012 is a leap year, February of that year will have 29 days. So from 1/1/2011-12/31/2011 you have 365 days. From 1/1/2012 - 5/1/2012, you have 121 days (122 days if you include 5/1/2012).

I have my spreadsheet setup to where I can calculate the number of regular year and leap year days, however I can only do it for one date range. When I have multiple date ranges on a tab, I am unable to calculate for all of them. Sorry I am terrible at explaining. If I could include my spreadsheet to the thread it may make more sense.

 

[Dave Patton]

I am not sure what you require. Consider the following:
a) another way to id leap years
http://www.mrexcel.com/forum/excel-...ears-leap-year-visual-basic-applications.html

b) calculate the number of days

Excel 2010
	A	B	C
1	01-01-15	30-06-16
2
3	Days in leap year		182
4	Days not in leap year		365
5
3a

Cell Formulas
Range	Formula
C3	=SUMPRODUCT(--(MOD(YEAR(ROW(INDEX(A:A,A1):INDEX(A:A,B1))),4)=0))
C4	=SUMPRODUCT(--(MOD(YEAR(ROW(INDEX(A:A,A1):INDEX(A:A,B1))),4)<>0))

 

[Steve_R]

If Dave's response does not help:


The fact the we use different date formats dd/mm/yyyy and mm/dd/yyyy/ didn't help my initial understanding of the problem but I understand the difference now now. Why do you need it - maybe annual salary or other payment division by 365 or 366? The question is only curiosity. You do not need to answer as it is not necessary to know that to answer your question.

Calculating days that fall within the leap year and outside the leap year will be mildly complex (broadly):
- Identify whether the from year, the to year or a year or years between are leap year.
- break up the date ranges to before leap year, during leap year and after leap year.
- calculate the days in each component
- add them.

Excel takes all that in its stride with a normal calculation of days between dates.

Excel 2010

	A	B
1	From	6/06/2011
2	To	7/07/2013
3	Days(inclusive)	763

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B3 =B2-B1+1 <tbody> </tbody>

<tbody>
</tbody>

However, that does not break down to easily allow separation of the components of 366 days in 2012 and 397 days in the other years. The question now becomes, how complex does your formula need to become? Will your date ranges span long periods or will the duration always be less than two years?


If you need to add further information, there are two ways to put sample data on the forum in a way we can easily copy/paste to start working with your data:
The first is the recommended way:
Read Posting Aids on Guidelines for Forum Use
Choose an add-in (I find HTML Maker easy enough to use)
Following instructions on whichever add-in you choose to install
After installation, highlight the cells you want to copy to forum
and follow instructions to get a copy for the forum.
If using HTML make, this would be right-click one of the highlighted cells,
choose formula selection options then paste on the forum.
You will see HTML code when you paste but the result will be similar to above normal calcuation of days between dates.

The other method is to save your spreadsheet (personal data removed and sensitive data replaced) into a cloud location (e.g. Dropbox or OneDrive) and paste a link.
Warning: Some members may choose not open sample sheets posted via link.