name range

[Myriam]

Hi, everyone,
I need to define a name range that might change depending on
the leap year (if it includes it or not).
I have dates on Sheet1! Column A (3 consecutive years).
How can I specify the range to include only the cells of a particular year?
As always, thanks for any light you can give me.

 

[Myrna Larson]

Assuming every day of each year is listed and this workbook doesn't have to
calculate correctly in the year 2100, you could define the range for YEAR2004
by typing this in the RefersTo box:

=OFFSET($A$1,MATCH(DATE(2004,1,1),$A$1:$A$1200,0)-1,0,365+(MOD(2004,4)=0),1)

If you need to worry about 2100, it gets longer:

=OFFSET($A$1,MATCH(DATE(2004,1,1),$A$1:$A$1200,0)-1,0,
MATCH(DATE(2005,1,1),$A$1:$A$1200,0)-MATCH(DATE(2004,1,1),$A$1:$A$1200,0),1)

(all on one line, of course)

Or add the VBA IsLeapYear function (see below) to your workbook and use the
formula


=OFFSET($A$1,MATCH(DATE(2004,1,1),$A$1:$A$1200,0)-1,0,365+IsLeapYear(2004),1)

Function IsLeapYear(Yr As Integer) As Boolean
IsLeapYear = False
If Yr Mod 4 = 0 Then
IsLeapYear = True
If Yr Mod 100 = 0 Then
If (Yr Mod 400) Then IsLeapYear = False
End If
End If
End Function 'IsLeapYear

 

[Myriam]

Thank you so much. That's exactly what I needed!
Regards,

 

[Myriam]

Myrna,
I forgot to ask you, how do I include the entire row of the particular date?
Thanks again