Conditional Format - Letter and Date within same cell

[MelRJ]

Hi all,

I need some help creating a formula that will highlight a cell if the cell begins with the letter F and not A. I would like to be able to highlight past due cells as well as highlight those coming up within 2 weeks. I have half the equation but ma having some trouble on the second portion.

=AND(LEFT($G2,1)="F",G2<TODAY()-100)<today()-100)< strong="">


This is a portion of the tracker I am trying to add the above to:
F: 02/06/2015 F: 02/16/2015 F: 02/23/2015 F: 02/24/2015 F: 03/31/2015
A: 07/29/2014 A: 08/06/2014 A: 08/13/2014 A: 08/14/2014 A: 09/18/2014
A: 08/22/2014 A: 09/01/2014 A: 09/08/2014 A: 09/09/2014 A: 10/14/2014
A: 09/02/2014 A: 09/16/2014 A: 09/21/2014
A: 09/03/2013 A: 10/04/2013 A: 10/02/2013 A: 10/03/2013 A: 11/07/2013
F: 05/27/2015 F: 06/04/2015 F: 06/11/2015 F: 06/12/2015 F: 07/17/2015
A: 04/17/2014 A: 05/12/2014 A: 06/02/2014 A: 06/02/2014 A: 06/02/2014

So if the cell contains F and the date is past due highlight red. If the cell contains F and the date is coming up in two weeks highlight yellow. Otherwise no highlight.</today()-100)<>

 

[DebugGalpin]

Your F cells are going to be text so may need to reconstruct a date out of them first to get your formula

Cell date is date(right(A1,4),mid(A1,4,2),mid(A1,7,2))

You can then use your conditional formatting around that

 

[MelRJ]

Thank you! That worked perfectly.

=AND(LEFT(G2,1)="F",DATE(RIGHT(G2,4),MID(G2,4,2),MID(G2,7,2))<TODAY()-1)