formula compare date computer with date of column and highlight

[abdelfattah]

hello
i would formula if the date in my computer the same in column f then gives me in column c finish and highlight by red if the date in my computer less than column f then give me not finish and highlight green
input

output

 

[etaf]

you can use an IF() with TODAY()

=IF( F2 < = Today() , "Not Finished" , "Finished")

Then use a conditional formatting - 2 rules
Set rule 1 formula
F2 < = Today()
RED FONT

2nd Rule
F2 > Today()
Green Font

But I dont understand your Example
29th Aug is in the future (based on todays date 20th May
and so why is this finished ?
also
the other contents of column F are not valid dates

 

[abdelfattah]

thanks etaf and i'm sorry about make confusing i was hurry to do my post your are right every thing what said your formula not complete it gives me "not finish" even the empty cell moreover i want the formatting condition about column g and ,are you sure colored font not back?
a new output

 

[etaf]

sorry got the formula wrong way around
=IF(F2<TODAY(),"Finished","Not Finished")

For the Condtional format
you could use
RED
G1 = "finish"

Green
G1 = "Not Finish"

Book1
	E	F	G
1	first date	end date	case
2		5/1/20	Finished
3		5/2/20	Finished
4		5/3/20	Finished
5		5/4/20	Finished
6		5/5/20	Finished
7		5/23/20	Not Finished
8		5/24/20	Not Finished
9		5/25/20	Not Finished
10		5/26/20	Not Finished
11		5/10/20	Finished
12		5/11/20	Finished
13		5/12/20	Finished
14		5/23/20	Not Finished
15		5/24/20	Not Finished
16		5/25/20	Not Finished
17		5/26/20	Not Finished
18		5/17/20	Finished
19		5/18/20	Finished
20		5/19/20	Finished
21		5/20/20	Not Finished
22		5/21/20	Not Finished
23		5/22/20	Not Finished
24		5/23/20	Not Finished
25		5/24/20	Not Finished
26		5/25/20	Not Finished
27		5/26/20	Not Finished
28		5/27/20	Not Finished
29		5/28/20	Not Finished
30		5/29/20	Not Finished
Sheet1

Cell Formulas
Range	Formula
G2:G30	G2	=IF(F2<TODAY(),"Finished","Not Finished")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G	Expression	=G1="Not Finished"	text	NO
G:G	Expression	=G1="Finished"	text	NO

 

[abdelfattah]

hi, etaf i try to use from condition formatting but no changes i choose from condition formating choice number2 "Format cells that contain only" then

i choose list drop down this specific text and i write this =G4="not finish" and press format to choose color and press ok !

 

[etaf]

for 2007, 2010 , 2013 or 2016 excel version
Conditional Formatting

Highlight applicable range >>

select the column G


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=G1 = "not finish"


FormatÖ [Number, Font, Border, Fill] text font green
choose the format you would like to apply when the condition is true
OK >> OK

then add another rule

Highlight applicable range >>

select the column G


Home Tab >> Styles >> Conditional Formatting
New Rule >> Use a formula to determine which cells to format
Edit the Rule Description: Format values where this formula is true:


=G1 = "finish"


FormatÖ [Number, Font, Border, Fill] text font red
choose the format you would like to apply when the condition is true
OK >> OK

 

[Fluff]

Cross posted without links AGAIN!!

You know the rules, please comply.

 

[abdelfattah]

hi, Fluff as far as i know this is not the same subject the main post is the formula and highlight but my post in another forum is solve problem condition formatting that's why i don't provide the link in the beginning despite this
here the link
problem condition formating