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
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