Find row of date of previous year

M

Michael Singmin

Hello Group,

I have a column of dates spanning over 3 years and they are sorted to
the latest date.
I am looking for a worksheet function that will deliver the row of the
first date in the previous year.

Thanks,

Michael Singmin
 
S

scott

This might work. A1;A4063 is the listing of historical dates by date
and looks for January 1st of the previous year.


=MATCH(DATE(YEAR(TODAY())-1,1,1),(A1:A4063),0)
 
T

Tom Ogilvy

=MATCH(DATE(YEAR(TODAY())-1,1,1),A1:A16,1)+ISERROR(MATCH(DATE(YEAR(TODAY())-
1,1,1),A1:A16,0))*1

Assumes the data starts in A1.
 
T

Tom Ogilvy

that will give an error if the first day of the year isn't in the list.

the OP didn't specify, but just to clarify.
 
M

Michael Singmin

Hello all,

Some clarification,

The dates could be
15 Sep 03
21 Jul 03
2 Mar 03
11 Nov 02
17 Jun 02

I know how do this in VBA but wondered whether one could use Match.

Thanks,

Michael Singmin
=====================================================================
 
T

Tom Ogilvy

Sure. My formula assumed they were sorted ascending.

A slight adjustment and it returns 4 for your test data:

=MATCH(DATE(YEAR(TODAY())-1,12,31),A1:A17,-1)+ISERROR(MATCH(DATE(YEAR(TODAY(
))-1,12,31),A1:A17,0))*1
 
Top