Forum Discussion
Formula Help Request
Currently works:
- formula: when entering a date (1/1/2025) into C1, D1 = date +2 years (1/1/2027)
- conditional formatting: when C1 "does not contain blank", C1 cell is green
- conditional formatting: when D1 "does not contain blank", D1 cell is red
Problem:
- When C1 is blank, D1 shows "12/31/1901" and the cell is red
My question:
- How do I keep D1 blank when C1 is blank but still adhere to the above formula + conditional formatting?
Thank you.
It is not difficult, you need to first modify the formula in D1. In D1, use the following formula:
=IF(C1<>"", C1+2, "")
This formula can help you check if C1 is not blank. If C1 contains a valid date, it will add two years to the date. If C1 is blank, D1 will be blank, too. For your other questions about conditional formatting:
when C1 "does not contain blank", C1 cell is green
1. Select C1.
2. Go to the Home tab > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter this formula:=C1<>""
5. Set the formatting to green.
when D1 "does not contain blank", D1 cell is red
1. Select D1.
2. Go to the Home tab > Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter this formula:=D1<>""
5. Set the formatting to red.
Formula for D1 cell is same as HansVogelaar sir posted to his answer.
=IF(C1="","",EDATE(C1,24))For conditional formatting, use the following rules
=C1<>"" and apply range is C1. Use fill color green.
=D1<>"" and apply range is D1. Use fill color red. See the attached file.
=IF(C1="", "", EDATE(C1, 24))
