Lost Counting Dates

[PJC2013]

Hi,

I'm a bit lost with this one and my brain is now mush, I have a spreadsheet showing three school terms and how many children are in each term.
I want to show how many children are in the 'current' term based on today's date (which can be in any one of three terms, term dates are below)
Term One
01/04/2013 > 31/08/2013
Term Two
01/09/2013 > 31/12/2013
Term Three
01/01/2014 > 31/03/2014

So essentially I want to show, if today's date is between Term 1 then return X children value, or if today's date is between Term 2 then return Y children value, or if today's date is between Term 3 then return Z children value.

I've played around with COUNTIF, COUNTIFS, AND, SUMIF etc but with no luck returning the value in the relevant term.

Any suggestions would be greatly appreciated.

[etaf]

need to see how your data is laid out really

can you attach an example spreadsheet ?

[ChemistB]

How is your data set up? Where is the number of children located? Are the two dates (start and end) in the same cell? Can you upload an example spreadsheet (Go advanced>Manage attachments)?