Find row of date of previous year

  • Thread starter Thread starter Michael Singmin
  • Start date Start date
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
 
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)
 
=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.
 
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.
 
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
=====================================================================
 
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
 
Back
Top