return financial period if value between 2 dates
 

I have the following table set up on sheet2
Periods Start End
1 1-Apr-06 20-Apr-06
2 21-Apr-06 18-May-06
3 19-May-06 15-Jun-06
etc.

On sheet 1, when the date is entered in Column A, I want a function in
Column B to show what financial period that date would be in.

i.e.
A B
05-10-2006 2

return financial period if value between 2 dates
 

One way ..

Sheet2's reference table as posted is assumed within A1:C4

In Sheet1,

Assume dates running in A2 down

Put in B2, array-enter (press CTRL+SHIFT+ENTER):
=INDEX(Sheet2!$A$2:$A$4,MATCH(1,(A2=Sheet2!$B$2:$ B$4)*(A2<=Sheet2!$C$2:$C$4),0))
Copy B2 down. Adapt the Sheet2 ranges to suit the extent of your actuals
before copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tmirelle" wrote:
I have the following table set up on sheet2
Periods Start End
1 1-Apr-06 20-Apr-06
2 21-Apr-06 18-May-06
3 19-May-06 15-Jun-06
etc.

On sheet 1, when the date is entered in Column A, I want a function in
Column B to show what financial period that date would be in.

i.e.
A B
05-10-2006 2

return financial period if value between 2 dates
 

Try this:

=INDEX(Sheet2!A2:A4,MATCH(Sheet1!A1,Sheet2!B2:B4,1 ))


"tmirelle" wrote:

I have the following table set up on sheet2
Periods Start End
1 1-Apr-06 20-Apr-06
2 21-Apr-06 18-May-06
3 19-May-06 15-Jun-06
etc.

On sheet 1, when the date is entered in Column A, I want a function in
Column B to show what financial period that date would be in.

i.e.
A B
05-10-2006 2

return financial period if value between 2 dates
 

I'm getting a value of #NA for all

"Max" wrote:

One way ..

Sheet2's reference table as posted is assumed within A1:C4

In Sheet1,

Assume dates running in A2 down

Put in B2, array-enter (press CTRL+SHIFT+ENTER):
=INDEX(Sheet2!$A$2:$A$4,MATCH(1,(A2=Sheet2!$B$2:$ B$4)*(A2<=Sheet2!$C$2:$C$4),0))
Copy B2 down. Adapt the Sheet2 ranges to suit the extent of your actuals
before copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tmirelle" wrote:
I have the following table set up on sheet2
Periods Start End
1 1-Apr-06 20-Apr-06
2 21-Apr-06 18-May-06
3 19-May-06 15-Jun-06
etc.

On sheet 1, when the date is entered in Column A, I want a function in
Column B to show what financial period that date would be in.

i.e.
A B
05-10-2006 2

return financial period if value between 2 dates
 

"tmirelle" wrote:
I'm getting a value of #NA for all

That's probably because you didn't "array-enter" the formula (press
CTRL+SHIFT+ENTER), as advised in my response. Go back and click inside the
formula bar for B2, then press CTRL+SHIFT+ENTER (instead of just pressing
ENTER) to confirm the formula. If you did it correctly, you'd see that Excel
wraps curly braces { } around the formula (see in the formula bar), and the
formula will work properly. With it properly entered in B2, copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

return financial period if value between 2 dates
 

Thought I had... but it worked this time.

Thanks very much

"Max" wrote:

"tmirelle" wrote:
I'm getting a value of #NA for all

That's probably because you didn't "array-enter" the formula (press
CTRL+SHIFT+ENTER), as advised in my response. Go back and click inside the
formula bar for B2, then press CTRL+SHIFT+ENTER (instead of just pressing
ENTER) to confirm the formula. If you did it correctly, you'd see that Excel
wraps curly braces { } around the formula (see in the formula bar), and the
formula will work properly. With it properly entered in B2, copy down.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

return financial period if value between 2 dates
 

Welcome. Glad you got it working.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"tmirelle" wrote in message
...
Thought I had... but it worked this time.

Thanks very much