Contains 29 February
 

Hi All

I need to create a formula which will calculate whether the period between two specified dates contains the 29th February and return "Y" or "N".

Date one is located in cell B164, date two in cell D164 - the first date will always be earlier than the second.

I need to avoid macros as our network securities will not allow them.

Any help would be much appreciated!

Regards,

Amy

Contains 29 February
 

Hi Amy,

Am Fri, 17 Apr 2015 01:48:53 -0700 (PDT) schrieb
:

Date one is located in cell B164, date two in cell D164 - the first date will always be earlier than the second.

what is the smallest and the largest difference between these dates?
Can you provide some examples?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Contains 29 February
 

Hi again,

Am Fri, 17 Apr 2015 12:06:45 +0200 schrieb Claus Busch:

what is the smallest and the largest difference between these dates?
Can you provide some examples?

try it with:
=IF(SUMPRODUCT(--(DAY(ROW(INDIRECT("A"&B164&":A"&D164)))=29),--(MONTH(ROW(INDIRECT("A"&B164&":A"&D164)))=2)),"Y", "N")

If you have 8 0r more years between the dates you can use:
=SUMPRODUCT(--(DAY(ROW(INDIRECT("A"&B164&":A"&D164)))=29),--(MONTH(ROW(INDIRECT("A"&B164&":A"&D164)))=2))
to find how much leap days you have in this period.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Contains 29 February
 

Il giorno venerdì 17 aprile 2015 10:49:02 UTC+2, ha scritto:
I need to create a formula which will calculate whether the period between two specified dates contains the 29th February and return "Y" or "N".
Date one is located in cell B164, date two in cell D164 - the first date will always be earlier than the second.
I need to avoid macros as our network securities will not allow them.
Any help would be much appreciated!

Hi Amy,

=INT(SUM(--(DAY(EOMONTH(ROW(OFFSET(INDIRECT("A"&B164),0,0,D16 4-B164+1,1)),0))=29))/29)
CSE (Array Formula)

--
Ciao! :)
Maurizio