Hi,

Is there a way to use excel to figure out if there is a leap day within
the range of two dates? The spreadsheet will have many different dates
with varying ranges.

Thanks to any help you can give me.

My gut reaction is to use VBA to loop through the years and test whether
the 29/02 (or 02/29 if you are american) is a valid date

hope this helps


--
tony h
------------------------------------------------------------------------
tony h's Profile: http://www.excelforum.com/member.php...o&userid=21074
View this thread: http://www.excelforum.com/showthread...hreadid=567636

With your earlier date in A1 and the later date in B1, put this formula
in C1:

=IF(B1-A1=ROUNDUP((A1-60)/1461,0)*1461+60-A1,"Leap day included","No
leap day")

Change the messages to suit, and copy it down for other dates.

If either date is a leap day then this is taken as "within" the range -
if you meant "between" the dates (i.e. excluding the start and end
dates) you should change it to:

=IF(B1-A1ROUNDUP((A1+1-60)/1461,0)*1461+60-A1,"Leap day included","No
leap day")

NOTE: Excel recognises 29th February 1900 as a leap day, though this is
incorrect - the formula goes along with Excel's definition. If you are
working in the 1904 date system, then the formula may not work.

Hope this helps.

Pete

wrote:
Hi,

Is there a way to use excel to figure out if there is a leap day within
the range of two dates? The spreadsheet will have many different dates
with varying ranges.

Thanks to any help you can give me.

Thanks so much. This worked with the exception of if the cells were
blank it would say leap year included so I edited like this so it will
display nothing if B1 is empty (not literally, but essentially):
=IF(B10,IF(B1-A1=ROUNDUP((A1-60)/1461,0)*1461+60-A1,"Leap day
included","No Leap Day"),"")

Thanks for your help. I never would have figured that out on my own.

Pete_UK wrote:
With your earlier date in A1 and the later date in B1, put this formula
in C1:

=IF(B1-A1=ROUNDUP((A1-60)/1461,0)*1461+60-A1,"Leap day included","No
leap day")

Change the messages to suit, and copy it down for other dates.

If either date is a leap day then this is taken as "within" the range -
if you meant "between" the dates (i.e. excluding the start and end
dates) you should change it to:

=IF(B1-A1ROUNDUP((A1+1-60)/1461,0)*1461+60-A1,"Leap day included","No
leap day")

NOTE: Excel recognises 29th February 1900 as a leap day, though this is
incorrect - the formula goes along with Excel's definition. If you are
working in the 1904 date system, then the formula may not work.

Hope this helps.

Pete

wrote:
Hi,

Is there a way to use excel to figure out if there is a leap day within
the range of two dates? The spreadsheet will have many different dates
with varying ranges.

Thanks to any help you can give me.

Thanks for feeding back.

Essentially, there are 4 * 365 + 1 days between leap days (i.e. 1461)
and the first Leap Day in Excel's reckoning is 29/02/1900, which is 60
days after its reference date (1st Jan 1900), so that explains the
constants used. The ROUNDUP function finds the number of days after A1
until the next Leap Day, and the IF checks to see if the number of days
between A1 and B1 exceeds or equals this.

Hope this helps - I didn't expect B1 to be empty, but you've found a
way of coping with that.

Pete

wrote:
Thanks so much. This worked with the exception of if the cells were
blank it would say leap year included so I edited like this so it will
display nothing if B1 is empty (not literally, but essentially):
=IF(B10,IF(B1-A1=ROUNDUP((A1-60)/1461,0)*1461+60-A1,"Leap day
included","No Leap Day"),"")

Thanks for your help. I never would have figured that out on my own.

Pete_UK wrote:
With your earlier date in A1 and the later date in B1, put this formula
in C1:

=IF(B1-A1=ROUNDUP((A1-60)/1461,0)*1461+60-A1,"Leap day included","No
leap day")

Change the messages to suit, and copy it down for other dates.

If either date is a leap day then this is taken as "within" the range -
if you meant "between" the dates (i.e. excluding the start and end
dates) you should change it to:

=IF(B1-A1ROUNDUP((A1+1-60)/1461,0)*1461+60-A1,"Leap day included","No
leap day")

NOTE: Excel recognises 29th February 1900 as a leap day, though this is
incorrect - the formula goes along with Excel's definition. If you are
working in the 1904 date system, then the formula may not work.

Hope this helps.

Pete

wrote:
Hi,

Is there a way to use excel to figure out if there is a leap day within
the range of two dates? The spreadsheet will have many different dates
with varying ranges.

Thanks to any help you can give me.