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

hydac12

New member
Joined
Feb 15, 2013
Messages
4
Reaction score
0
Points
0
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.


View attachment Excel help Database.xlsx
 
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"))
 
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!!!
 
Apologies, yours does just that! I didn't have any filled in the table!!!

I cannot say thank you enough!!
 
Last edited:
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.
 
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.


Yeah I had already done that and it works brilliantly now!

Thanks a lot for your help!
 
Back
Top