Results 1 to 6 of 6

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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. #3
    Quote Originally Posted by NBVC View Post
    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. #4
    Apologies, yours does just that! I didn't have any filled in the table!!!

    I cannot say thank you enough!!
    Last edited by hydac12; 2013-02-18 at 10:19 AM.

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    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. #6
    Quote Originally Posted by NBVC View Post
    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!

Posting Permissions

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