Microsoft Office Forums Formula to calculate Leap Year
 Register FAQ Search Today's Posts Mark Forums Read

#1
09-11-2011, 02:06 AM
 USAOz Windows 7 32bit Office 2010 32bit Novice Join Date: Jan 2011 Location: Sydney Australia Posts: 9
Formula to calculate Leap Year

I need a formula that will calculate if a year that falls with in a fiscal (financial) year is a Leap Year or not.

For example if I enter the start date for a fiscal year in a cell (e.g. 1 July 2011) and the end date of the fiscal year in another cell (e.g. 30 June 2012) I'd like a message to be displayed in the formula cell saying "Leap Year" if ANY part of the date range falls in a Leap Year (as it would in this example) or a blank cell (no text) if the entire date range does NOT fall in any Leap Year.

Any ideas on how this calculation could be done?
#2
09-11-2011, 02:45 PM
 Colin Legg Windows 7 32bit Office 2010 32bit Expert Join Date: Jan 2011 Location: UK Posts: 369

Hi,

This article from MS shows how to calculate if a given year is a leap year:
Code:
```http://support.microsoft.com/kb/214019

=IF(OR(MOD(A1,400)=0,AND(MOD(A1,4)=0,MOD(A1,100)<>0)),"Leap Year", "NOT a Leap Year")```
If you are always entering entire fiscal years, then you just need to use this formula to reference the year of the end date. Supposing your start date, 1st July 2011, is in cell A1, and your end date, 30th June 2012 is in cell A2, you would change the formula as follows:
Code:
`=IF(OR(MOD(YEAR(A2),400)=0,AND(MOD(YEAR(A2),4)=0,MOD(YEAR(A2),100)<>0)),"Leap Year", "")`
If you want to be able to enter any set of dates, which may or may not span multiple years, then it is possible but the formula will become considerably more complicated.
#3
09-11-2011, 04:58 PM
 USAOz Windows 7 32bit Office 2010 32bit Novice Join Date: Jan 2011 Location: Sydney Australia Posts: 9

Thank you VERY much - you're a legend!

 Tags fiscal year, leap year

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post msproject2010 Project 1 08-01-2011 04:29 AM ridonkulous5 Excel 1 03-23-2011 04:49 PM janak Excel 6 12-12-2010 10:08 PM Cpat Excel 2 06-09-2010 02:07 PM jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 09:46 PM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top