Help Please, For New Cattle Management System

[Harrywatson]

In column B I have numerous dates down the column displayed as --/--/----.

In column D I then have the ages relative to the dates in column B using the formula:

=DATEDIF(B1,NOW(),"m") &" months, " & DATEDIF(B1,NOW(),"md") &" days "

So I know have ages in column D such as '23 months 7 days' or '13 months 22 days'.

What I would like to know please is how to format the cells so that when there is an age that is under 16 months but over 11 months. The cell then becomes highlighted.

Eg. 12 months 4 days would be highlighted. 15 months 15 days would be a highlighted cell. 16 months 2 days wouldn't be a highlighted cell. Neither would be 10 months 6 days.

The answer to this would be greatly appreciated thanks Please ask questions if you need any more details. Thanks so much

 

[shg]

You could use

=(EDATE(B2, 11) < TODAY()) * (EDATE(B2, 16) > TODAY())

as a conditional formatting formula to highlight the cell.

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E
1	Animal	DOB	Age in months
2		Jan 01, 2014	0.7	0 months, 21 days	0
3		Aug 05, 2013	5.6	5 months, 17 days	5
4		Feb 26, 2013	10.9	10 months, 27 days	10
5		Jan 17, 2013	12.2	12 months, 5 days	12
6		Nov 18, 2012	14.2	14 months, 4 days	14
7		Sep 19, 2012	16.2	16 months, 3 days	16
1b

Excel 2003

Cell Formulas
Range	Formula
C2	=($G$1-B2)/30.25
C3	=($G$1-B3)/30.25
C4	=($G$1-B4)/30.25
C5	=($G$1-B5)/30.25
C6	=($G$1-B6)/30.25
C7	=($G$1-B7)/30.25
D2	=DATEDIF(B2,TODAY(),"m") &" months, " & DATEDIF(B2,TODAY(),"md") &" days "
D3	=DATEDIF(B3,TODAY(),"m") &" months, " & DATEDIF(B3,TODAY(),"md") &" days "
D4	=DATEDIF(B4,TODAY(),"m") &" months, " & DATEDIF(B4,TODAY(),"md") &" days "
D5	=DATEDIF(B5,TODAY(),"m") &" months, " & DATEDIF(B5,TODAY(),"md") &" days "
D6	=DATEDIF(B6,TODAY(),"m") &" months, " & DATEDIF(B6,TODAY(),"md") &" days "
D7	=DATEDIF(B7,TODAY(),"m") &" months, " & DATEDIF(B7,TODAY(),"md") &" days "
E2	=DATEDIF(B2,TODAY(),"m")+0
E3	=DATEDIF(B3,TODAY(),"m")+0
E4	=DATEDIF(B4,TODAY(),"m")+0
E5	=DATEDIF(B5,TODAY(),"m")+0
E6	=DATEDIF(B6,TODAY(),"m")+0
E7	=DATEDIF(B7,TODAY(),"m")+0

Your question is similar to your previous questions.

You could refer to the numbers in Column C or E for the Excel Conditional Format's formula criteria.
N.B. Column D is Text.

 

[Harrywatson]

Thanks, I only realised after I had posted this that someone had replied to my other question with a formula that I understood, I used yours also and they both work great thanks a lot