Forum Discussion
Solved
IF Statement? Or conditional formatting... How?
1) Im trying to set excel up so that Column C has the date of a service that has been completed, Column D Im using the formula =EDATE(C2,3) to calculate the due date in 3 months. This is working okay however I have copied this formatting down the column but if in the next row Column C is empty it still throws a random date into Column D in that corresponding row. I don't want it to show anything in that cell until I complete the entry in Cell C in that corresponding row.
2) I have managed to set up conditional formatting so that Column D shows red if overdue up to 1 month, yellow if coming due in the next week or that week. In column E I have "Quote sent?" and I want it to change the date (background) in Column D to green if i say yes. I worked out how to make column cell (E) green if i say yes but not the cell to its left ie Column D cell. I want that formula to work the whole way down. Ie Im essentially wanting to check off if its done by turning it green so it overrides the red/yellow conditional formatting i have set for the date in Column D to green based off the response in column E
Queston 1:
=IF(C2="", "", EDATE(C2, 3))
Question 2:
Select D2:D100 or however far down the data extend. D2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$E2="Yes"
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.
1 Reply
Queston 1:
=IF(C2="", "", EDATE(C2, 3))
Question 2:
Select D2:D100 or however far down the data extend. D2 should be the active cell in the selection.
On the Home tab of the ribbon, click Conditional Formatting > New Rule...
Select 'Use a formula to determine which cells to format'.
Enter the formula=$E2="Yes"
Click Format...
Activate the Fill tab.
Select a fill color.
Click OK, then click OK again.