Hi Team,
I need some help on my excel problem. On the attached file I wanted the excel to copy the value on column A if the value in column D is within the range anywhere between H2 to BH2. The formula I used does not seem to work right because if I have dates that fall between weekday (Wednesday to Thursday) or on a weekend, excel could not identify.
Thanks in advance
Not quite sure what you want to do. Does this formula achieve it?
=IF(AND($D3>=H$2,$D3<I$2),$A3,"")
If not then please give some examples, exactly what values you exepct to see in the first few rows for example
Audere est facere
Hi Daddylonglegs,
Thanks for your response.
What I'm looking for is excel to look for its correct cell in column H to BH (Chart)
Ex: if D17 = 10/28/2013 then it should then it should find its correct cell anywhere in H17 to BH17 with a TRUE value. So excel should have a TRUE value in AY17 because that cell has a date range of 10/26/13 to 11/1/2013.
Thanks again.
OK then I think you just want the formula I suggested but with TRUE in place of A3, i.e. in H3 copied across and down
=IF(AND($D3>=H$2,$D3<I$2),TRUE,"")
I tried but its not pointing me to the right cell. I was expecting that the TRUE value should show at the begining of the blue cells.
pls help daddylonglegs
Last edited by Ex-xcel; 09-02-2013 at 10:20 AM.
OK, looks like I'm a column out because I was assuming that your dates were the start of each week rather than the end date.......
Try this amended version in H3 copied across and down
=IF(AND($D3<=H$2,$D3>G$2),TRUE,"")
Wow it worked.
However there are some rows like from 174 to 185 where my date range are from 2012 and its still giving me a true value even if it should not because its already out of range. Any fix for this?
But should should do the trick for the meantime.
What's the start date for the first week? Assuming it's H$2-6 then this version should fix that...
=IF(AND($D3<=H$2,$D3>IF(G$2="",H$2-7,G$2)),TRUE,"")
or if you just want to exclude any date before 2013 try this one
=IF(AND($D3<=H$2,$D3>G$2,YEAR($D3)>2012),TRUE,"")
Last edited by daddylonglegs; 09-02-2013 at 11:25 AM.
wow. you rock! thanks heaps daddylonglegs
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks