Please help with a date formula.

[Souny]

Good morning all,

I need some help with a formula related to date.

In my Excel file, I have dates in Cells D1 to DL1, and each date in each
cell represents Monday (e.g. 01/11/10) of the week.

I also have dates in Cells A2 and B2. The dates in A2 and B2 can be any
date, but the date in A2 (e.g. 01/11/10) is earlier than the date in B2 (e.g.
01/27/10).

What I need help is a formula in Cells D2 to DL2. The formula in those
cells will evaluate whether the date in adjacent cell (e.g. adjacent cell of
D2 is D1) falls between the dates in Cells A2 and B2. If it is, the formula
will input "Y" in that cell. For example, using the sample dates from above
for A2 and B2, the formula will input "Y" in Cells E2, F2 and G2.

The formula that I currently have is:

=if(and(weeknum(d$1,2)<=weeknum($a2,2),weeknum(d$1,2)>=weeknum($a2,2)),"Y","")

That formula works, but it does not work when I get to the cells (e.g.
BD2L2) with a different year. The formula will also input "Y" in those
respective cells. Using the sample dates from above, the formula inputs "Y"
in Cells BE2, BF2 and BG2.

I am using Excel 2003 and Windows XP.

Thanks.

 

[David Biddulph]

Why are you using WEEKNUM?
Why not just =IF(AND(D$1<=$B2,D$1>=$A2),"Y","") ?
I notice that you used A2 twice in your formula and I assume that the first
should be B2?

 

[Souny]

David,

Thanks for response.

First of all, you are correct about the B2. It should be B2 on the second
one, not A2.

Secondly, I can not use the formula that you suggested because the date on
D1 represents Monday of the week, instead of Sunday of the week. My week
starts on Monday and ends on Sunday, and I want the formula to input "Y" in
the cells when the dates in A2 and B2 fall into those weeks.

I have tried the following formula, and somehow it did not work.

=if(and(d$1>=$a2,date(year(d$1),month(d$1), day(d$1)+6)<=$b2),"Y","")

Thanks.

 

[Souny]

David,

I just want to share with you that I got it figured out. Below is the
formula.

=IF(AND(AND(YEAR($A2)=YEAR(D$1),WEEKNUM(D$1,2)>=WEEKNUM($A2,2)),AND(YEAR(D$1)=YEAR($B2),WEEKNUM(D$1,2)<=WEEKNUM($B2,2))),"Y","")

Thanks again for your response.