Trying to write a formula in cell J9 that gives the answer "no" if all conditions in cells g9 and i9 are met or answers "yes" if any are not met. The formula I've written is: =IF(I9="","YES",IF(TODAY()>I9+1825,"YES",IF(TODAY()<I9+1825,"NO",IF(G9="","yes")))) G9 and I9 contain dates or can be blank. It seems that this only works if the condition in I9 is met. What am I doing wrong?
IF statements work linearly, so as soon as Excel finds one that works, it will put in your 'value if true' answer. So with your formula, if I9 is blank, it will return 'Yes' regardless of the status of G9.
It's not clear from your formula exactly when you want 'Yes' - do you want it only if G9 and I9 are blank? Is there a Today() check for G9 too?
Try using some AND statements and/or OR statements in your formula. Something like this (just an example - though you might be able to work out your answer from it):
Formula:
Please Login or Register to view this content.
Hope that helps - please ask if it's not clear.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
I'm not entirely sure that I follow you, but...
=IF((AND(ISNUMBER(G9),TODAY()<I9+1825)),"No","Yes")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Please Login or Register to view this content.
If someone has helped you then please add to their Reputation
Sorry for confusing everybody, perhaps it would be easier if I just explained what I'm trying to do and see if somebody can come up with a formula that will do it.
In the column marked "Assessed" there will be a date. In the column marked "used" there will be a date. I want a formula to be placed in the third colum marked "can continue" to work out whether dates written in the first two columns are within a certain time period, say 5 years, from todays date. If both are then the formula returns the statement "Yes" if either or both are not then the formula returns the statement "No". The time period difference could be different for the first column to todays date (say 5 years) than the second column to todays date (say 3 years). It is also possible that one or both of the columns will be blank in which case I also need the statement "No" to be returned in the third column. Example spreadsheet attached. Hope somebody out there can understand me!
OK. So, is this it then: (both dates must be present and < 5 years from today for YES, otherwise NO):
=IF(AND(A2>EDATE(TODAY(),-60),B2>EDATE(TODAY(),-60)),"Yes","No")
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks