Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-11-2011, 02:06 AM
USAOz USAOz is offline Formula to calculate Leap Year Windows 7 32bit Formula to calculate Leap Year Office 2010 32bit
Novice
Formula to calculate Leap Year
 
Join Date: Jan 2011
Location: Sydney Australia
Posts: 9
USAOz is on a distinguished road
Cool 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?
Reply With Quote
  #2  
Old 09-11-2011, 02:45 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Formula to calculate Leap Year Windows 7 32bit Formula to calculate Leap Year Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

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.
Reply With Quote
  #3  
Old 09-11-2011, 04:58 PM
USAOz USAOz is offline Formula to calculate Leap Year Windows 7 32bit Formula to calculate Leap Year Office 2010 32bit
Novice
Formula to calculate Leap Year
 
Join Date: Jan 2011
Location: Sydney Australia
Posts: 9
USAOz is on a distinguished road
Default

Thank you VERY much - you're a legend!
Reply With Quote
Reply

Tags
fiscal year, leap year

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula to calculate Leap Year Over allocated with only 1300 hours in a year?!!! msproject2010 Project 1 08-01-2011 04:29 AM
Creative Ways for a year-to-year comparison??? ridonkulous5 Excel 1 03-23-2011 04:49 PM
Formula to calculate Leap Year Year janak Excel 6 12-12-2010 10:08 PM
Can't calculate formulas Cpat Excel 2 06-09-2010 02:07 PM
Formula to calculate Leap Year How to get cells to calculate time? jrasche2003@yahoo.com Excel 2 02-09-2007 07:10 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 07:15 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft