S
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.
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.