# Thread: Excel Vlookup, if, match between two dates?? Add 6 months onto dates in calandar??

1. ## Excel Vlookup, if, match between two dates?? Add 6 months onto dates in calandar??

Hello all,

After using your forum and trying to bodge together several of your answers in order to achieve what I wish I've given up and thought I'd post here for help. Now I'm not sure if this is possible?? I'm sure some of your gurus can answer!

I have a spreadsheet, Down Column B, starting at B3, are the dates of the week commencing days for a few years. 2013 - 2015.

i.e. 31/12/2012 - 07/01/2013 - 14/01/2013 - 21/01/2013

Now I'd like to put a date into C2 and it for it to, down the C column, every 6 months put the value "Red", and every 1 month, put that value "blue" from the date in C2.

The above is what I'm trying to do if anyone knows how to do this straight up then don't hesitate to answer, it'll be much appreciated!!

The method I'm attempted to do the above is as follows, two worksheets one "Register" one "Table", the above is on the worksheet "Register":

I have a separate worksheet "Table" where I've got a list of the dates that are the date in C2 + 1 month using: =DATE(YEAR(B1),MONTH(B1)+1,DAY(B1))

B1 Refers to =Register!C2

And underneath I have =DATE(YEAR(B55),MONTH(B55)+6,DAY(B55))

B55 Refers to =Register!C2

So now I have created a list of dates that are e.g

13/02/2013
13/03/2013
13/04/2013
13/05/2013
13/06/2013

and

13/02/2013
13/08/2013
13/02/2014
13/08/2014
13/02/2015

Now I've tried a variety of different formulas bodged together, the one I'm trying to do, that I think is most likely to work, with a bit of tweaking possibly, is:

=IF(AND(Table!B1:B54>B3,Table!B1:B54<B4),"Red","nope"), IF(AND(Table!B55:B94>B3,Table!B55:B94<B4),"Blue","nope")

Where hopefully it will look up from the list of dates I have, and see if the any of the dates match between the two Week Commencing dates, and if it matches from the first list it will return red, if it matches from the second list it will return blue.

However I get #VALUE!

Any help/ideas/humour to lighten my mood??

I've attached the spreadsheet as it'll probably make more sense than my attempt to explain.

Excel help Database.xlsx

2. Judging but what it looks like you are trying to do with your formula, perhaps?

=IF(COUNTIFS(Table!\$B\$56:\$B\$94,">"&B3,Table!\$B\$56:\$B\$94,"<"&B4),"Blue",IF(COUNTIFS(Table!\$B\$2:\$B\$54,">"&B3,Table!\$B\$2:\$B\$54,"<"&B4),"Red","nope"))

3. Originally Posted by NBVC
Judging but what it looks like you are trying to do with your formula, perhaps?

=IF(COUNTIFS(Table!\$B\$56:\$B\$94,">"&B3,Table!\$B\$56:\$B\$94,"<"&B4),"Blue",IF(COUNTIFS(Table!\$B\$2:\$B\$54,">"&B3,Table!\$B\$2:\$B\$54,"<"&B4),"Red","nope"))

You sir are a genius!! You've got further than I ever did/could!!!

Just one small blip - when the 6 month's interval date lands on the same date as the 1 month's interval date - only the 1 month interval date is shown.

Is there a way to show the 6 month's interval date as a priority over the 1 month date?

A big thank you again - you've saved me a lot of stress!!!

4. Apologies, yours does just that! I didn't have any filled in the table!!!

I cannot say thank you enough!!

5. perhaps if you change the > to >= and the < to <= at each occurrence in the formula, you might get better results. I am not sure because I can't see/test your data from my current device.

6. Originally Posted by NBVC
perhaps if you change the > to >= and the < to <= at each occurrence in the formula, you might get better results. I am not sure because I can't see/test your data from my current device.

Thanks a lot for your help!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•