Microsoft Excel IF rules

[funkylad20]

I am trying to show in a cell in Excel that if a child a born between two dates they are eligible for someting, with the result shoing as a 'Y' . If born outside of these two dates, then they are not eligible and a 'N' will appear in the box. Can anyone crack the formula? To make it more complex, I am wanting to have different date parameters dependent on which school term it relates to (this will change dependent on what is listed in another cell in the spreadsheet).

[up4it]

How about this ....

=IF(D5<I10,"N",IF(D5>J10,"N","Y"))

D5 = the date you are trying to check on
I10 = the lower date of the range you are trying to check against
J10 = the higher date of the range you are trying to check against

Basically if D5 falls within I10 and J10 then the answer will be 'Y' if not it will be 'N'

I hope it helps

[funkylad20]

No, tried it and it would not work.
One near to that I got was:

=IF(N2<31/8/5,"N")&IF(N2>31/12/3,"N","Y")

But it has come back as FALSEN in the cell....!

[up4it]

I tried mine and it did work !!!
N.B. you may need the cells formatted as 'Date' cells

Your formula looks like it will not work as in the first IF condition there is no 'Value if false' condition
- I would never recommend hardcoding dates into a formula but rather have them in another cell and reference them there

I am guessing that you want to use this year in and year out, hence the dates changing, so pick 2 cells that are going to contain your upper and lower dates to compare against and stick them in the 2 cells (right mouse click on these 2 cells, select format cells and make sure they are shown as 'Date')

Then presumably against each child there is a cell which has a date (which you want to compare against the upper/lower dates) and then next to that there is a empty cell where you want your formula to answer Y or N

In this empty cell use the formula I first posted
=IF(D5<I10,"N",IF(D5>J10,"N","Y"))

To make it easier ....
=IF(Each childs date cell<lower date range cell,"N",IF(Each childs date cell>upper date range cell,"N","Y"))

This is basically an IF condition within an IF condition
All you will ever have to do is for each school term change the 2 cells with the upper and lower date ranges i.e. for the start/end of a school term

If you still cant get it to work then I am guessing you are doing something wrong as it works fine for me

[up4it]

Let me know when you sort it out as I cant understand why you cant get my formula to work

If all else fails about the best Excel help I have found is the forum at ....
http://www.ozgrid.com

[funkylad20]

Hmmm, thanks for this. I guess I will brush over this one for an hour or two as I STILL cannot get it to work!

See if you can, I'll explain some detail...

3 and 4 year old children are entitled to a grant BUT it is dependent on when they are born. They are eligible the TERM after their third birtday....so for the forthcoming Autumn Term, a child born BETWEEN 1st September 2003 and 31st August 2005 will be eligilbe (so I want a 'Y' in the cell).

A child with the DOB as 30/08/05 will be 'Y' (just)
A child with the DOB as 01/09/05 will be a 'N'
A child with the DOB 10/08/02 will be a 'N' and
A child with the DOB 12/12/03 will be a 'Y'.

I have no idea, but it never changes from a Y to a N, even if I put my own DOB in (I am 26!)

I have swapped the dates around in the formula, swapped the < for an > and cannot suss it out...

[up4it]

What is the cell you are using for the childs date of birth?
- I assume this will be the whole column but just pick one of the cells

"a child born BETWEEN 1st September 2003 and 31st August 2005"
- I assume you mean Sept 2003 and Aug 2004

What is the cell where you want these dates i.e.
What cell for the Sept date? i.e. earliest date
What cell for the Aug date? i.e. latest date

[dave2297]

=IF(AND(VALUE(A1)>=37865,VALUE(A1)<=38595),"Y","N")


works for me.....and this is presuming that the dob is in cell A1, that Col A1 is formatted as a date and that you enter into it as dd/mm/yy

[funkylad20]

GENIUS! You have crakced it.
I thought I had read somewhere that excel gives dates a 'number' (i.e. 37865 and 38595) as it is essentially coded data.
How did you find out these numbers?

[up4it]

That looks like it should work too although I dont like the hardcoding of the date in the formula, makes it harder to change for the next year they want to use the same spreadsheet

[dave2297]

There is no such thing as a 'date' in Excel, it is just a number formatted for our ease of use.

If you enter a date into a 'date' formatted cell and, in the cell next to it type
=value(cell ref of cell you just typed into)
it will return the number.
The formula i typed before just does that on the fly

[dave2297]

I agree with up4it.....if the dates change then you would have to change the values in the formula....best to reference cells with dates in!!!

[funkylad20]

In an academic year, I will only ever need 6 dates, so it is just 6 'date' codes (the spreadsheet is only one academic year).

Fancy a challenge?!
I now need that cell which has a 'Y' or an 'N' to change dependent on which term it is.

I have a cell in the spreadsheet detailing:
Autumn 2008
Spring 2009
Summer 2009

...and the eligibility dates for each are:
Autumn 2008 - 01/09/03 - 31/08/05 - DONE! (thanks!)
Spring 2009 - 01/01/04 - 31/12/05 - ???
Summer 2009 - 01/04/04 - 31/03/06 - ???

I am guessing now (and learning!) that the last 4 'date codes' are:
37987
38717
38078
38807

SO, I need it to work these out, dependent on the reading from another cell with the term date text (Autumn 2008, Spring 2009, Summer 2009)....

[dave2297]

so........let me get this right

Are you saying that there is n't a field with dates in - just a column with term names ie Autumn 2008 etc?

[funkylad20]

That is correct, and it needs to change with dependent on which term it is referring to.

For example, I needed to put the term dates in automatically if 'Autumn Term 2008' was entered, and change if the 'Autumn Term 2008' was swapped to 'Summer Term 2009" so I used to following:

=IF(R12="Autumn Term 2008","1st September 2008", "") & IF(R12="Spring Term 2009","12th January 2009", "") &IF(R12="Summer Term 2009","20th April 2009", "")

[funkylad20]

Can nobody suss this one out?!

[up4it]

Should be easy enough to do but I am not exactly following what you mean

... and there is the potential every time the term changes the dates will also e.g.
Autumn 2008 - 01/09/03 - 31/08/05
- will this always be 1st Sept to 31 Aug
Spring 2009 - 01/01/04 - 31/12/05
- will this always be 1st Jan to 31st Dec
etc.

I suppose you could have a fixed value and then increment by 365 per each year incremented but what about leap years?

It may just be easier to do this in a macro rather than have a complex formula in every cell

It would be easy to sort it out for you if you sent the excel sheet you are working on so I could see exactly what you are trying to achieve

[funkylad20]

Thanks Up4it, but managed to suss it out with the help of kempie!

http://www.theanswerbank.co.uk/Technology/Comp uters/Question608945.html

I am really sad to admit this but I am loving this massive learning curve. I thought I was good at Excel before, but am learning so much from you guys! Had a meeting today with the people this is for and have some amendments to make tonight, so watch out for anymore cries for help!!