Percentage of the Year Formula

Options
Luke W.
Luke W. ✭✭✭✭✭
edited 07/28/21 in Formulas and Functions

Good Afternoon!

I'm currently constructing some calculations and I would like some help with a formula that will show the "percentage of year" that a goal will achieve if completed by it's due date.

Example:

  • Project Due Date = 6/1/21

If the project is completed on time (due date), then that project will have achieved goal attainment for 50% of the year (6 months of being completed for the remaining year).

Any help in creating a formula for this would be greatly appreciated!


Thanks,

Luke

Best Answer

  • Luke W.
    Luke W. ✭✭✭✭✭
    Answer ✓
    Options

    I was able to figure it out based on the input you gave Steven, thank you very much!


    The correct formula ended up being:

    =(DATE(YEAR([Project ETBR]@row), 12, 31) - [Project ETBR]@row) / 365

    If project due date was 1/1/21 then above formula would show 100%.

    If project due date was 12/31/21 then above formula would show 0%.


    Regards,

    Luke

Answers

  • Steven Stikons
    Options

    Hi Luke,

    Assuming your project due dates are in a column named "Project Due Dates" and the the formula you are writing is in the same row as the dates, you can use the following formula to calculate the fraction:

    =([Project Due Date]@row - DATE(YEAR([Project Due Date]@row), 1, 1)) / 365

    Then format the column/cell with the formula as percentage.

  • Luke W.
    Luke W. ✭✭✭✭✭
    Options

    Hi Steven,

    Thanks for helping out with this!

    That worked to a point, but I think the percentage calculation is a bit off.

    For example, the formula provided gives me a result of 0% with a Project Due Date of 1/1/21, when it should be 100%, since it will have been completed for 100% of the year .


    Does that make any sense?

  • Luke W.
    Luke W. ✭✭✭✭✭
    Answer ✓
    Options

    I was able to figure it out based on the input you gave Steven, thank you very much!


    The correct formula ended up being:

    =(DATE(YEAR([Project ETBR]@row), 12, 31) - [Project ETBR]@row) / 365

    If project due date was 1/1/21 then above formula would show 100%.

    If project due date was 12/31/21 then above formula would show 0%.


    Regards,

    Luke

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!