% Complete Today Vs Actual % Complete

Options

Hi,Does any one know if there is any calculations which says % Complete date as on TODAY by calculating Planned start, Planned finish and “current date”. (lets call it % Complete Today)

I was looking for a formula which gives below calculations. (I’m comparing it with actual % Complete)

[% Complete Today VS Actual % Complete]

Below is the scenario.

If % Complete Today is less than or equal to actual % , then Green

If % Complete Today is greater than actual % it is Yellow.

For "Red" If % Complete Today is greater than Actual% and same for Yellow but with a 10% extra to differentiate between yellow and red.

It will be really helpful if any one can get a formula.

Answers

  • boscod35
    boscod35 ✭✭✭
    Options

    Hi Daavid,

    Just a suggestion, this could be achieved through the conditional formating, Did you try that.

    Let me know,

    Thank you,

    Don

  • Vikas
    Vikas ✭✭
    Options

    Hi Don,


    I'm unable to try it with conditional formatting. Please guide me if you are able to make it.What I have tried is made Separate column for "% Complete Today" using formula =(TODAY() - [Start Date]@row) / ([End Date]@row - [Start Date]@row). Later tried to compare Actual % Complete with this column. can colour code for Green and yellow but not for Red. For Red I'm expecting % Complete Today to be at least 10% higher than Actual % Complete. I used the below formula.

    =IF([% Complete Today]@row < [Actual % Complete]@row, "Green", IF([% Complete Today]@row > [% Complete]@row, "Yellow"))

  • Genevieve P.
    Options

    Hi @Daavid

    You can multiply your Actual % Complete by 1.1 to see what it would be 10% higher, then use this to say that if the % Complete Today is greater than this multiplication, return Red. Otherwise, Yellow.

    Try this:

    =IF([% Complete Today]@row <= [Actual % Complete]@row, "Green", IF([% Complete Today]@row > ([Actual % Complete]@row * 1.1), "Red", "Yellow"))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now