Y or N depending upon age and date criteria

[mickjjuk]

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

[NBVC]

Please test this formula for accuracy:

Please Login or Register  to view this content.

[daddylonglegs]

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")

[NBVC]

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!

[mickjjuk]

Hi Many thanks

I have tested this out on my worksheet and although it works for some of the dates it is incorrect for others

I have attached a sample worksheet as an example. Column D is the data prior to inputting your formula. As you can see there are quite a few differing now in column C

eg row 1. the age is 3 and the dob is Sep07, hence using my criteria this should be Y

Regards

[NBVC]

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

[mickjjuk]

Hi

Thanks

This seems to work great.

Could you take the trouble to explain some of the logic behind it for me. especially the 1,4,9:4,9,13

Do the 1,4,9 equate to the Jan, Apr, Sept months, what is the following 4,9,13 relating to.

Sorry, but I like to try and at least understand the principles involved rather that just copy formula blindly. Help me get a better understanding for the future

Regards

[mickjjuk]

Yes I have tried Daddylonglegs and it does match, see my answere to him.

But many thanks for your time and efforts it is much appreciated

[daddylonglegs]

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

[mickjjuk]

Ok, Thanks, I look forward to it

[daddylonglegs]

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......

[mickjjuk]

Many thanks for the explanation, clever stuff!!

And certainly something very useful for future reference.

Regards