Hi
I have a column with dates in the following format. 01-01-13 etc... In a sheet called January.
I would like to accomplish in this sheet is to color code any cell that is not from January 2013
=IF(AND(MONTH(A1)=1;YEAR(A1)=2013);"TRUE";"FALSE") //note I have to use ; and not , separator
I think I am looking for something like the above statment though not quite.
Something like this?
=IF(AND(MONTH(A1)<>1;YEAR(A1)<>2013)
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
..And of course as my friend Vlady mentioned(Thanks), no need of IF in Cf.
=AND(MONTH(A1)<>1;YEAR(A1)<>2013)
For some reason I can't get it to work as expected (or so i think).
If I test it I get the following...
=IF(AND(MONTH(A1)<>1;YEAR(A1)<>2013);"TRUE";"FALSE")
01-01-2013
15-01-2013
02-02-2013
15-02-2013
11-11-2011
12-12-2012
FALSE
FALSE
FALSE
FALSE
TRUE
TRUE
02-02-2013 and 15-02-13 should both be TRUE...right?
Ok. YES you are correct. We need OR. Not AND...
=OR(MONTH(A1)<>1;YEAR(A1)<>2013)
YES that sure did it.
I do have a couple more questions though.
If I only want to color code dates in the column and nothing else how would I go about doing that? (or just ignore empty cells would be fine too)
If I wanted to create a sheet that stored the month and year values how would I go about doing that? Or maybe I would not as I don't seem to be able to make a reference to another sheet in a conditional statement?
1) =OR(AND(MONTH(A1)<>1;YEAR(A1)<>2013);A1>0)
2) You CAN use a reference to another sheet using Named ranges. In this case i need more explanations and better will be to have a sample workbook.
=OR(AND(MONTH(A1)<>1;YEAR(A1)<>2013);A1>0)
Though empty cells are now ignored the statement seems to return true to all dates no matter month or year.
I have attached a sample workbook with to sheets, "dates" and "lookatdates". I was imagining the following...
=OR(AND(MONTH(A1)<>lookatdates!b1;YEAR(A1)<>lookatdates!A1);A1>0)
sample_workbook_dates.xlsx
Hi samson1000
Try: =AND($A1<>"",OR(YEAR($A1)<>2013,MONTH($A1)<>1))
Regards Kevin
Merged Cells (They are the work of the devil!!!)
2 CF
See the example.
Something strange is at works!
If I add the following to a new sheet it works.
=AND(C3<>"";OR(YEAR(C3)<>2013;MONTH(C3)<>1)) //as the formating begins in C3 in my sheet
However if I ad it to an existing sheet it behaves somewhat randomly with the color coding. Even though the statement returns false on a date (I tested it) it will in some cases format (color code) the cell even when false is returned?
ps
@fotis1991, I see how the referencing is working, thanks.
Sorry, it is working now.
I just can't get it through my head that it somehow seems like I can't highlight a whole column if it starts in C3 that is.
So awesome, thanks.
Have you tried clearing all formats, re apply them, some times this works. Or post a worksheet with dummy data.
I'll try clearing another time but for now it's all good. Appreciate it though.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks