Find the row number with a date in it
 

I am trying to find the row number, to use in a formula, of a row with
a date 365 days or 1 year ago.

I need this to perform some cacluations on the last 12 months worth of
data. Is there a simple excel function to do this or will I need a
makro?

Thanks

Find the row number with a date in it
 

=match(today()-365,rng,0)

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


wrote in message
oups.com...
I am trying to find the row number, to use in a formula, of a row with
a date 365 days or 1 year ago.

I need this to perform some cacluations on the last 12 months worth of
data. Is there a simple excel function to do this or will I need a
makro?

Thanks

Find the row number with a date in it
 

In case there is not an exact matching date try:

=MIN(IF(F1:F31=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),F1:F31))

Array entered with Control + Shift + Enter instead of just Enter otherwise
you will get a date of January 1 1900

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


wrote in message
oups.com...
I am trying to find the row number, to use in a formula, of a row with
a date 365 days or 1 year ago.

I need this to perform some cacluations on the last 12 months worth of
data. Is there a simple excel function to do this or will I need a
makro?

Thanks

Find the row number with a date in it
 

Of course, unlike Bob, I did not read your question properly. To get the
Row number try:

=MATCH(MIN(IF(F6:F36=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),F6:F36)),F6:F36,0)

Still Array entered. If your data does not start in Row 1 then add
+(whatever the Row number before the start of your data is) at the end of
the formula.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


"Sandy Mann" wrote in message
...
In case there is not an exact matching date try:

=MIN(IF(F1:F31=DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())),F1:F31))

Array entered with Control + Shift + Enter instead of just Enter otherwise
you will get a date of January 1 1900

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


with @tiscali.co.uk


wrote in message
oups.com...
I am trying to find the row number, to use in a formula, of a row with
a date 365 days or 1 year ago.

I need this to perform some cacluations on the last 12 months worth of
data. Is there a simple excel function to do this or will I need a
makro?

Thanks