Checking if dates is between two others

[RichardMGreen]

Hi all

I'm fairly certain that this has been asked before, but searching didn't give me anything.
I've got two columns (B & C) which contain the start and end dates for various weeks in the format dd/mm/yyyy. Column E has the week "name" in the format "d - d mmm".
I'm trying the get a cell to return the week name from column E if I input a specific date.

This is what I've got so far:-
=IF(AND(Dates_Data!$B$2:$B$100>=D3,Dates_Data!$C$2:$C$100<=D3),Dates_Data!$E$2:$E$100)
which has been done as an array formula, but it just gives me a False.
Anyone any ideas where I've gone wrong?

Here's a sample of data (I hope)

Week	Start Date	End Date	This week?	Column Name
1	01/04/2010	07/04/2010	FALSE	1 - 7 Apr
2	08/04/2010	14/04/2010	FALSE	8 - 14 Apr
3	15/04/2010	21/04/2010	FALSE	15 - 21 Apr
4	22/04/2010	28/04/2010	FALSE	22 - 28 Apr
5	29/04/2010	05/05/2010	FALSE	29 - 5 May
6	06/05/2010	12/05/2010	FALSE	6 - 12 May
7	13/05/2010	19/05/2010	FALSE	13 - 19 May
8	20/05/2010	26/05/2010	FALSE	20 - 26 May

The dates run all the way down to row 100.

 

[cherambane]

Can u explain once again Which out put you want in "This week" column???

 

[RichardMGreen]

I need to return the column name (which is column E in my spreadsheet and the last column on the table above).

 

[RichardMGreen]

Anyone, please?

 

[cherambane]

Simple!!! Go to format cells
and custom format Type: "dddd"

 

[RichardMGreen]

Not really, that will give me a day.
I'm looking to return the correct column name for any given date.
So, from the dates above, a date of 02/04/2010 will return "1 - 7 Apr"

 

[cherambane]

I Hope This will work...

Week	Start Date	End Date	This week?	Column Name
1	01/04/10	=b2+6		=TEXT(B2,"d -")&TEXT(C2,"d mmm")
2	=c2+1	=b3+6		=TEXT(B3,"d -")&TEXT(C3,"d mmm")
3	=c3+1	=b4+6		=TEXT(B4,"d -")&TEXT(C4,"d mmm")
4	=c4+1	=b5+6		=TEXT(B5,"d -")&TEXT(C5,"d mmm")
5	=c5+1	=b6+6		=TEXT(B6,"d -")&TEXT(C6,"d mmm")
6	=c6+1	=b6+6		=TEXT(B7,"d -")&TEXT(C7,"d mmm")

 

[jasonb75]

Excel Workbook
	A	B	C	D	E	F	G	H
1	Week	Start Date	End Date	This week?	Column Name
2	1	01/04/2010	07/04/2010	FALSE	1 - 7 Apr		18/04/2010	15 - 21 Apr
3	2	08/04/2010	14/04/2010	FALSE	8 - 14 Apr
4	3	15/04/2010	21/04/2010	FALSE	15 - 21 Apr
5	4	22/04/2010	28/04/2010	FALSE	22 - 28 Apr
6	5	29/04/2010	05/05/2010	FALSE	29 - 5 May
7	6	06/05/2010	12/05/2010	FALSE	6 - 12 May
8	7	13/05/2010	19/05/2010	FALSE	13 - 19 May
9	8	20/05/2010	26/05/2010	FALSE	20 - 26 May
Sheet

 

[RichardMGreen]

Cherambane - Already got that bit, but thanks anyway.

Jason - works perfectly, cheers.