Thread: Y or N depending upon age and date criteria

Hi

I have a spreadsheet calculating if a child is eligible to receive funding. (ie, all children aged 3 or 4)

In Col A I have DOB's , in Col B I have the formula =DATEDIF(A1,TODAY(),"y") and in Col C I have the formula =IF(B1>2,"Y","N")

However I need the result in Col C to take into account the following criteria:

on TODAYS date or whenever the spreadsheet is open.

Funding is only available from 1/9(1st Sept) this year following a chids 3rd birthday if the child was born between 1/4 and 31/8

for example: if a child was born on 09/04/08 then today he would not recieve funding, but would from 1/9 this year.

or

Funding is only available from 1/1 (1st Jan) this year following a chids 3rd birthday if the child was born between 1/9 and 31/12

or

Funding is only available from 1/4 (1st April) this year following a chids 3rd birthday if the child was born between 1/1 and 31/3

I hope the above is a clear enough explanation.

Can anyone help please

Please test this formula for accuracy:

=IF(OR(B1>3,AND(B1=3,OR(AND(TODAY()>=DATE(YEAR(TODAY()),9,1),A1>=DATE(YEAR(TODAY()-3),4,1),A1<=DATE(YEAR(TODAY())-3,8,31)),AND(TODAY()>=DATE(YEAR(TODAY()),1,1),A1>=DATE(YEAR(TODAY())-3,9,1),A1<=DATE(YEAR(TODAY())-3,12,31))))),"Y","N")

I think you can just adjust the DOB, try this version

=IF(DATEDIF(DATE(YEAR(A1),LOOKUP(MONTH(A1),{1,4,9;4,9,13}),0),TODAY(),"Y")>2,"Y","N")

I should know better than to answer date related problems when you're around ... but I tried... and I think, at least, I get the same result .....NOT!

I think if you use Daddylonglegs' formula, you'll find they all match

just on my way out of the office - I'll reply later.......

Sorry, slipped my mind.....

In this part

LOOKUP(MONTH(A1),{1,4,9;4,9,13})

The {1,4,9;4,9,13} part effectively creates a lookup table in the formula - the month number of A1 is looked up in the "first row", i.e {1,4,9} and matched with the greatest value smaller than or equal to the lookup value, e.g. 1, 2 and 3 match with 1, then 4, 5, 6, 7 and 8 match with 4, the rest match with 9. For each of these the relevant value is returned from the second row {4,9,13}, so if A1 is in February the 2 matches with 1 and therefore the 4 is returned. If A1 is November then that matches with 9 and 13 is returned. The effect of this is that the whole date part, i.e. this

=DATE(YEAR(A1),LOOKUP(MONTH(A1),{1,4,9;4,9,13}),0)

converts any date to the end date of the period, e.g. for February 2009 dates, as I said above, the LOOKUP will return 4 so the date is

DATE(2009,4,0)

When you use a zero as the day that is the equivalent of the previous month so that date is actually 31st March 2009.

Similarly for November 2008 you get

=DATE(2008,13,0)

which is the equivalent of the last day of 2008, 31st December 2008.

So the effect, overall is to convert the DOB to the end date of the relevant period and then work out the age using that date, so for a February date the age won't be 3 until 31st March etc......