Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a date formula.
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. BD2:DL2) 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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a date formula.
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? -- David Biddulph "Souny" wrote in message ... 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. BD2:DL2) 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a date formula.
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. "David Biddulph" wrote: 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? -- David Biddulph "Souny" wrote in message ... 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. BD2:DL2) 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. . |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Please help with a date formula.
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)=WE EKNUM($A2,2)),AND(YEAR(D$1)=YEAR($B2),WEEKNUM(D$1, 2)<=WEEKNUM($B2,2))),"Y","") Thanks again for your response. "Souny" wrote: 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. BD2:DL2) 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) |