How to calculate a percent of tasks completed WTD and MTD based on percent of month complete

Ron Geiser

I have a set number of tasks that individuals are expected to complete for each day, week and month. I have combined all of the tasks to show a percent of tasks completed. however, the issue with is this approach is that it does not account for the percent of the week or month currently complete. what i really what the formula to tell me is what percent of tasks have they completed at that current moment. For example, the top field in the summery sheet says 22% complete. However, this individual completed 100% of the required tasks when this screen shot was taken.

Genevieve P.

Hi Ron,

I'm not sure that I quite understand what the percentages is referring to... when you say that the individual in the top of the summary (FOWD) has completed 100% of that week's tasks, how can you tell?

I presume that you are adding up the numbers in the blue columns and dividing by the target column's number to get the percentage, is that correct? But instead, it sounds like you're looking to have the numbers in the blue columns divided by the numbers in the yellow columns next to them.

What about using the SUM function like this:

=SUM([Beginning of shift hand off 3]1, [setting plan 3]1, [Gembas 15]1, [DAF 3]1, [End Shift Hand off 3]1) / SUM([Beg Wt Score]1, [Plan Wt Score]1, [Gemba Wt Score]1, [DAF Wt Score]1, [End Wt Score]1)

You'll just need to swap out the row references for each person. Is this what you were looking to do? If not, it would be helpful to have a bit more explanation of how the completed tasks are being logged and how the expected tasks are being displayed.

Thanks!

Genevieve

Ron Geiser

Thank you for your reply @Genevieve P,

This is how I have set up my process flow:

I have set up a sheet that has 10 tasks that each supervisors is required to fill out each day (Screen shot below). The rows in pink are for a different process. Each row automatically pushes an update request to the supervisor ( i have a different sheet for each supervisor based on their shift pattern) at the times listed in the sheet to update the attachment. The attachment is a form that feeds several other sheets, gets summarized in that set and feed the blue columns in the master sheet above (original screen shot). The yellow columns are how I am weighting each task, its simply multiplying the blue column by 1 or 2.

What I am wanting to do is calculate the real time percent complete based on completion of tasks vs the expected # to date. This is what i want the calculation to do, Ill use today as an example. Scenario 1, It is currently Monday @ 2:45pm, assuming the supervisor completed all the tasks through 1pm below the percent complete would say 100% for the day (6 out of 6 tasks). Scenario 2, the supervisor missed Gemba 1, so the percent complete would say 83% for the day (5 out of 6 tasks). I then want this to continue for the week and then month.

I hope this help clarify what I am trying to due.

Genevieve P.

Hi Ron,

In all honesty I'm not sure that there's a way to track what's completed based on the time as Smartsheet doesn't currently have a Timestamp functionality or a formula (like the TODAY formula) that checks the current time. Please submit an Enhancement Request for this when you have a moment! There may be other third party applications that you can integrate with Smartsheet to achieve this.

However in regard to tracking what's completed per Week / Month, this is more manageable. In order for Smartsheet to see the percentage that is complete, there does need to be a number for it to compare the data to though... does that make sense? It seems like you've set that up as your Target column in the other sheet, but the Target is only showing the Target for the entire week, not the Daily Target.

To find the current data for the week, we could use a WEEKDAY function to figure out what day of the week today is, and then based on that, have a different target during the week depending on the day. For example, Monday's target is 6, Tuesday's is 12, etc. That way the Target column will only display 30 for Friday, Saturday, and Sunday.

The WEEKDAY function starts with Sunday as 1, so you'd want to put this formula in your Target column for the Week:

=IF(WEEKDAY(TODAY()) = 2, 6, IF(WEEKDAY(TODAY()) = 3, 12, IF(WEEKDAY(TODAY()) = 4, 18, IF(WEEKDAY(TODAY()) = 5, 24, 30))))

This says, if Today is Monday, return 6 as the Target. If it's Tuesday, return 12... etc. Keep in mind that the TODAY function requires the sheet to be updated in some way to recognize what date Today is, however that includes having a cell-link update the sheet.

Is this more what you're looking to do? Could you maybe copy/paste the formula you're currently using in the Sheet Summary field?