# IF formula to outline Status in RED when 2 dates collide from the same variant/task

Options

For example I need RED Status when HBM collides with another HBM from a different task and start at the same date. Basically need to avoid date clashed from the same task HBM with HBM, Excavating with Excavating clashes to be advised in RED of the clashes , however without automatically changing the dates. Picture for understanding. Also I need support with how to automatically change completion percentage for each variant.

Many thanks.

• Employee
Options

Hi @Relly

Do you need the tasks to show Red if the Start Date is anywhere within another task? Or is it only if the date is the exact same Start Date as another row?

If it's only the exact Start Date matching, you can use the following formula:

=IF(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, =Start@row) > 1, "Red")

However notice that rows 3 and 4 in my image above also clash with the first task, just later on. Same with the HBM second task. An alternative formula would be:

=IF(OR(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, <=Start@row, Finish:Finish, >=Start@row) > 1, COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, >=Start@row, Finish:Finish, <=Finish@row) > 1), "Red")

In this instance, we're looking for if the current Start Date appears within another task with the same assigned category (so row 2, which shows red) OR if the current task spans across other tasks (the first Excavating row and the first HBM row).

In regards to automating the % complete, how do you know when a task is done? Do you have another status column on the sheet that users manually fill out to let you know?

Cheers!

Genevieve

October 8 - 10, Seattle, WA | Register now

• ✭✭✭✭✭✭
Options

Need additional screenshot showing the column names for those columns.

• Options

Hi Mike, Thank you for your prompt reply. Please see attached the screenshot. Sorry it doesn't have to be outlined as long as indicates the status in red. Many thanks.

• Employee
Options

Hi @Relly

Do you need the tasks to show Red if the Start Date is anywhere within another task? Or is it only if the date is the exact same Start Date as another row?

If it's only the exact Start Date matching, you can use the following formula:

=IF(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, =Start@row) > 1, "Red")

However notice that rows 3 and 4 in my image above also clash with the first task, just later on. Same with the HBM second task. An alternative formula would be:

=IF(OR(COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, <=Start@row, Finish:Finish, >=Start@row) > 1, COUNTIFS([Assigned To]:[Assigned To], [Assigned To]@row, Start:Start, >=Start@row, Finish:Finish, <=Finish@row) > 1), "Red")

In this instance, we're looking for if the current Start Date appears within another task with the same assigned category (so row 2, which shows red) OR if the current task spans across other tasks (the first Excavating row and the first HBM row).

In regards to automating the % complete, how do you know when a task is done? Do you have another status column on the sheet that users manually fill out to let you know?

Cheers!

Genevieve

October 8 - 10, Seattle, WA | Register now

• Options

The first formula works perfectly . I am beyond happy. Thank you very much Genevive!

• Employee
Options