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.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    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

Answers

  • Mike TV
    Mike TV ✭✭✭✭✭✭
    Options

    @Relly

    Need additional screenshot showing the column names for those columns.

  • Relly
    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.

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    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

  • Relly
    Options

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

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Glad I could help! 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!