Hi Lee

Take a look at the undocumented Datedif function on Chip Pearson's site
http://www.cpearson.com/Excel/datedif.aspx

If you have the child's DOB in A1, enter the following formulae in B1, C1
and D1
=DATEDIF($A1,TODAY(),"y")&" "&DATEDIF($A1,TODAY()*1,"ym")

=DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())+6,D AY(TODAY())),"y")&" "
&DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())+6,D AY(TODAY())),"ym")


=DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"y")&" "
&DATEDIF($A1,DATE(YEAR(TODAY()),MONTH(TODAY())-6,DAY(TODAY())),"ym")

B1 will hold the child's actual age
C1 will hold the upper limit (age + 6 months)
D1 will hold the lower limit (age - 6 months)

Now, select B1FormatConditional Formatting
Cell isBetweenC1 and D1 Format Amber
Add
Cell isLess thanD1 Format Green
Add
Cell isGreater thanC1 Format Red
--
Regards
Roger Govier

"Lee West" wrote in message
...
I have a spreadsheet that i need to use to calculate children's reading
ages
against their real ages with a 6 month swing either way, using a 3 colour
system.

For example. if a child is 8 years and 6 months old (Cell Ref B4 and
listed
as a 2 decimal point number), and their reading age is in the range 8
years
to 9 year 2 months, I need that to show amber. If it's one month either
side
of that range, i need it to show red if it's under and green if it's over.

I've got it working with specific number using less than, equal to or
greater than formuals, but it will only show amber for the exact DOB ie if
a
child is 8.6 in B4, it will only show amber if the number in C4 is
identical.

Does this make sense?

I also need to figure out how to create a custom format to take into
account
there are 12 months in a year and not just 10 when using normal numbers.

Any help would be supremely appreiciated