I really have no preference... whichever way is possible. I was attempting to write a logical test with limited success but a macro may well be more suitable. My apologies,i meant copy into column N, not N2.
logic is always possible , you should look at what you want and try to push the truth values about, until you see the full truth, try asking some of the questions in reverse, it may help you formulate a sometimes unusual but neat test...
if you are asking about the possibilities of a logic statement then the code above may be quite daunting as it involves a complete understanding not just of logic but the workings of the if()..
If J2 Does NOT contain "Exam Cancelled" or "Further possession required" then N2 equals B2 plus 28 days.
If J2 is blank then N2 is blank.
If B2 is blank then N2 is blank.
Ideally, in addition, if B2 is beyond todays date (ie date has passed) then N2 will again equal B2 plus 28 days
and got this
Code:
n=b+28 when b>now and when j is not "Exam Cancelled" or "Further possession required"
and special case when j="" or b="" then n=""
or this
Code:
when b="" or j="" or j="Exam Cancelled" or "Further possession required" and b<=now then n="" otherwise n=b+28
that should all fit into an if()
the stucture of if() is
if(test,action if true,action if false)
so the outcome part is easy
if(test,"",b+28)
the test is the complex bit
or() takes values to test separated by commas and and does like wise so the embedding of an or and the and is important...
we have or(b="" , j="", j="Exam Cancelled",j= "Further possession required" )
and "and(b<=now)"
the and goes on the outsidelike this
and(thing1,thing2)
so AND(b<=now,or(b="" , j="", j="Exam Cancelled",j= "Further possession required" ))
Thankyou Opus and Incidentals for your time... i am albeit very slowly starting to understand how it all works. i consider myself fairly computer literate but this is (literally) like another language to me!
I am getting this error (see attached pic) when i enter this into the cell. since i dont doubt your programming knowledge, i suspect is a silly mistake at my end. What do i need to change in order for this to work? The date column is currently set to ^date^ in the format cells category.
Also, this file will need to be stored and opened from our (very slow) server and so the smaller the filesize can be the better.... Any ideas on how to reduce the size with all these formulas in? i read somewhere if you apply formula or fomatting to a column as opposed to copying to each cell it might reduce the size of the file?
Thank you Incidentals, there is no longer an error but its not giving me exactly what i want, it is showing a date in N2 even though the date has not yet passed. I have been playing around with it trying to make it work but still cant make much sense of it.
Could you please take another look at it, I may not have explained correctly?
This is what im trying to achieve,
1. if column J does not equal "cancelled" and does not equal "possession" and column B is blank or date in column B has not yet passed then column N is blank
2. if column b date has passed and column J does not equal "cancelled" and does not equal "possession" then date in column N is date in column B +28 days
IF(OR(J<>"Cancelled - See Comments", J<>"Possesion Req'd - See Comments",B<NOW(),"")AND(B>=NOW(),J<>"Cancelled - See Comments", J<>"Possesion Req'd - See Comments"),B+28))
im so proud of myself! i manged to (sort of) understand it and get it to work!
Thankyou for all your time and explanations everyone... i finally used this code:
Code:
=IF(OR(B2>TODAY(),B2="",J2="Cancelled - See Comments",J2="Possesion Req'd - See Comments"),"",B2+28)