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)



Michael Singmin wrote:

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

=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.

--
Regards,
Tom Ogilvy

"Michael Singmin" wrote in message
...
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

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.

--
Regards,
Tom Ogilvy

"scott" wrote in message
...
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)



Michael Singmin wrote:

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

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
================================================== ===================

"Tom Ogilvy" wrote:

=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.

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
--
Regards,
Tom Ogilvy

"Michael Singmin" wrote in message
...
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
================================================== ===================

"Tom Ogilvy" wrote:


=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.

Thanks Tom,

Neat and instructive.

Michael

"Tom Ogilvy" wrote:

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