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

Relly

Screenshot 2022-11-29 14.49.43.png

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.

Genevieve P.

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

Mike TV

@Relly

Need additional screenshot showing the column names for those columns.

Relly

Screenshot 2022-11-30 07.24.50.png

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.

Genevieve P.

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

Relly

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

Genevieve P.

Glad I could help! 🙂