Formula for % on Track for Yearly Goal by Day of Year

anna.peters

We are trying to calculate if we are on track for the hours expected to work by the end of the year, per day. We are tracking this for two groups of people but weirdly enough, the formulas come out identical each day.

I use the following fields to calculate my estimation.

YTD Accumulated Total Expected Annual Hours: =([Expected Annual Hours]@row / 52) * [Week number]@row

Expected Annual Hours: =SUM([Expected Annual Hours]1:[Expected Annual Hours]27) * 0.7

YTD / Today's Goal : =([Expected Annual Hours]30 / 365) * YEARDAY(TODAY())

The final calculation to show the expected annual hours is as follows:

=(([YTD Accumulated Total Expected Annual Hours]30 * 0.7) / [YTD/Today's Goal]# * 100)

The multiplication of 70% is necessary, but I am wondering if my parentheses are in the wrong spot, missing, or if I'm writing this wrong entirely.

Weirdly, the result is always ~69.29% and identical to the same sets of formulas tracking a second group of people with different hours entirely.

Paul Newcome

Are you able to provide some screenshots for context?

anna.peters

Sure thing! Let me know if this is helpful or if there are particular screen shots that would be more efficient. The bottom row is purely summary while the top rows are entries by individual.

Paul Newcome

The percentage will be the same across projects because of how you have the whole workflow set up.

Basically you are saying we SHOULD have this many hours YTD based on today's date in comparison to the total for the year. Today's date will always be a certain percentage of the year regardless of how many hours are expected to be worked.

anna.peters

Ahhh, that makes sense. What would you suggest changing so it reflects where we are currently rather than should be?

Jgorsich

It seems like what you are trying to determine is what percent of your expected annual hours you've actually worked:

(actual hours)/(expected hours)

with expected hours increasing throughout the year based on the date and actual hours increasing due to actually working those hours.

If that is the case, somewhere you need to record your actual hours worked - which I don't see in your screenshot at all.

anna.peters

Thank you!