Previous Topic Next Topic

One cell will turn green when the two other cells match

[Solved/Closed]
Report
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
 -
 diestra123 -
Hello,
I am keeping a register of staff in our department. What I want to happen is for the cell (where the name is) to turn green when the date opposite the name (reporting date /date back from leave) matches the cell with formula (=today()), sort of like an alarm.
To further illustrate:
A1 = name of employee
B1 = date back from leave/reporting date
C1 = =today()

such that when B1 matches C1, A1 will turn green.
Thanks for the help!
Related:

7 replies

Answer 1 / 7
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
@ rizvisa1

That's 2 posts of yours that I have seen which are not helpful. Anyone can write eg: "use Conditional Format for that" or is it that you can not supply the answer!!

@ diestra 123

For your rule, select column A or a range of cells that you wish to apply the rule to > New rule > Use a formula to determine which cells to format.
Then for your rule: =AND($A1<>"",$B1=$C1) apply the formatting you require.
Answer 2 / 7
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
thanks @rizvisa1 and Kevin@Radstock!

your inputs have both been very helpful.
the thing is, what i want to do is much more complex (at least for me) than what i had posted.
so could you please help me out again? been going in circles here. :(

i have 4 columns.
A column is for the name
B column is the start date of leave period (from)
C column is the end date of leave period (to)
D column is for the reporting date

What i want to do is for the name to be in red when he is on leave, that is, from B date to C date. after C date, the name will be green to mean that the leave period is over but he has not reported yet. the name will be clear again once i have put the reporting date in column D.

thanks for the help!
Answer 3 / 7
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi diestra123

Select column A

Rule 1 = On leave.
=AND(ISNUMBER($C1),$C1>TODAY())

Rule 2 = Leave period is over.
=AND(ISNUMBER($C1),$C1<=TODAY())

Rule 3 = Reporting date has been entered.
=AND(ISNUMBER($D1),$C1<=TODAY())

Kevin
Report
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
thanks Kevin@radstock!

problem solved!!! whew! :)
Report
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi diestra123

Add this condition as well.

=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())
Answer 4 / 7
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi diestra123

No problem, thank you for the feed back.

Kevin
Report
Posts
10
Registration date
Monday October 22, 2012
Status
Member
Last seen
July 15, 2013
oooops, sorry Kevin, my problem is not completely solved.

opposite the names which already have leave schedule a month from now also turned red when i applied the format.
Report
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
well try this
1 Select Cell A1
2, Select column A
3. Go to conditional format
4 For red
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1>TODAY(),D1="")
It says b1 date should be equal or less than today's date and c1 date should be more that today.

5. For green
=AND(A1<>"", B1<>"",C1<>"",D1="",C1<=TODAY())
Is says that c1 date is less than or equal today's date


If you ask me, you should have 3 color
1. Still on valid leave
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1>TODAY(),D1="")

2. Today leave ends
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1=TODAY(),D1="")

3, Beyond Leave period
=AND(A1<>"",B1<>"",C1<>"",B1<=TODAY(),C1<TODAY(),D1="")
Report

this worked!!!
many thanks rizvisa1!!!
Answer 5 / 7
Posts
42
Registration date
Thursday January 31, 2013
Status
Member
Last seen
April 26, 2014
 9
Hi diestra123

Add this condition as well.

=AND(ISNUMBER($B1),$B1>TODAY(),$C1>TODAY())

Kevin
Report

thank you!!! everything's fine now!!
Answer 6 / 7

thanks again!!
Answer 7 / 7
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
use Conditional Format for that
Ask a question