advanced (i think) if statement help

[ukrockhit]

hiya all,

i am creating a spreadsheet and am having trouble with this if statement,

here is the prob:

the spreadsheet is about school students, and i will have students from year 7,8 and 9. I need to create an if statement which will look at the year group number, ie 7,8 or 9 will be in the options, and then if the date is correct for it to change the yeargroup number from 7 to 8, or 8 to 9, and 9 to be deleted (but forget about the year 9 for now)

i have created the following statement which should work but does not.
=IF(F5=8,IF(P2=30/10/2006,"9","8"),"8")
f5 is where the number 8 is
p2 has the date 30/10/2006 is (just todays date to test it!)
so looking at this satement is should look at the box f5 and check if it is 8, which it is, then look at p2 and check if the date is correct, which it is, and then SHOULD change the number 8 to 9, but does not, and keeps the number at 8,

the statement does say that if the requirements are not right to keep at 8 but it does not!

please help all, i can give additional info if required as i have tried adding " or removig in places with no difference!

[duane]

I think you may have to do this - but what exactly is entered in cell P2? Also, you don't expect to change the 8 in cell F5, right?

=IF(F5=8,IF(P2="30/10/2006","9","8"),"8")

[ukrockhit]

erm, well that was the idea to change 8 to 9 in cell f5 but forgot that lol, thank can be sorted later,
cell p2 says 30/10/2006 and i ahave formated it to be a date, but when it comes to making this for real, it will have =now() in p2 to hold the current date!

[teelim]

actually the main problem was with the date.

in the formula, the date ("30/10/2006") was recognized by Excel as Text instead of Date. So instead of changing this, I changed the input date to text.

hope the attached example can help you

*another thing I would like to add. I would recommend the use of TODAY() instead of NOW() as TODAY() only gives the date and not time (so no formatting is required). ~just a suggestion

[ukrockhit]

thanks i ill try that, thanks m8

will let you know how it goes.

[ukrockhit]

big thank m8 that works,
also dont mean to trouble but does anyone know if it is possible in an if statment for me to say if that date is correct and they are year 9, to delete the students name and results etc,

or can you using a different type of statment,

no probs if you not sure but would be a big help

apart from that i am ok with excel, as you can see as i thought that and wrote it but just had that bit of trouble

[teelim]

Glad I was able to assist a little

as for the following Q, I have rewritten the formula.

It is now like this:
-if "date"="30/10/2006", and if F9<=8, F9 +1, else "" (display nothing)

hope this is close to what you want

else, it would be useful you could attach an example.

[ukrockhit]

thanks again mate, will use that

[ukrockhit]

the formula works fine,but i was tringto make it so that it says,if f5 (for example) =9, then "" for f1,f2,f3,f4,f6?

is that possibleto do, or even same but just"" forf1 and not the others is fine!

[ukrockhit]

can anyone help with this?

[teelim]

Hi, sorry it took me a while to reply your post...

anyway, I am not quite sure how you want your spreadsheet but it sounds like you could to this:

you could try typing this in F1,F2,F3,F4,F6

=IF(F5=9,"",<whatever you want it to be>)

it would be helpful if you could attach a sample of your spreadsheet