Find the row number with a date in it

[ianc2003]

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

 

[Bob Phillips]

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

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Sandy Mann]

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

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk