Forum Discussion
Conditional Formatting and Data Validation
Priya125 actually the only part possible is the part you qualified with "if possible". Conditional formatting can NOT change values in the cell it will only change formatting. That said, you could change the Overdue? and Status columns to be formula based. The Overdue column can use a formula like:
=IFS([@[Completed?]]="","",AND([@[Completed?]]="No",[@[Due Date]]<TODAY()),"Yes",TRUE,"No")
Which will give you "" if col E is blank and then Yes/No if it is overdue.
Then your conditional formatting can just check for "Yes" in col F.
As for Status, can you define col H as "Time Allocated" so that any time in col H means the difference between "Not Started" and "In progress"; so then
=IFS([@[Completed?]]="","", [@[Completed?]]="Yes","Complete", [@[Due Date]]<TODAY(),"Overdue", [@[Actual Time to Complete (in hours)]]>0,"In progress", TRUE,"Not Started")
otherwise you would have to use Macro/VBA to do what you are asking.
Priya125 actually the only part possible is the part you qualified with "if possible". Conditional formatting can NOT change values in the cell it will only change formatting. That said, you could change the Overdue? and Status columns to be formula based. The Overdue column can use a formula like:
=IFS([@[Completed?]]="","",AND([@[Completed?]]="No",[@[Due Date]]<TODAY()),"Yes",TRUE,"No")
Which will give you "" if col E is blank and then Yes/No if it is overdue.
Then your conditional formatting can just check for "Yes" in col F.
As for Status, can you define col H as "Time Allocated" so that any time in col H means the difference between "Not Started" and "In progress"; so then
=IFS([@[Completed?]]="","", [@[Completed?]]="Yes","Complete", [@[Due Date]]<TODAY(),"Overdue", [@[Actual Time to Complete (in hours)]]>0,"In progress", TRUE,"Not Started")
otherwise you would have to use Macro/VBA to do what you are asking.
