Percentage progress based on dates in a ROW for staff training

pjcamish 

To calculate the percentage of tasks completed or in date for staff training and show this in column B, you can use a combination of Excel functions. Assuming your table is set up like this:

We'll assume the task dates start from column C and go through column T.

Step-by-Step Solution

1. Calculate the Number of In-Date Tasks: Use the IF and TODAY functions to determine if each task is up-to-date or not.
2. Calculate the Percentage: Count the number of up-to-date tasks and divide by the total number of tasks (19 in this case).

Formula for Column B

Here’s a formula that you can use in column B to calculate the percentage of in-date tasks for each staff member:

=SUMPRODUCT(--(C2:T2>=TODAY()-365)) / 19

Explanation:

• C2:T2>=TODAY()-365 checks if each task date is within the last year.
• --(C2:T2>=TODAY()-365) converts the TRUE/FALSE results to 1/0.
• SUMPRODUCT(--(C2:T2>=TODAY()-365)) counts the number of tasks that are in-date.
• / 19 divides by the total number of tasks to get the percentage.

If your tasks are only valid for a specific period (e.g., they need to be renewed every 3 months), adjust the date range accordingly.

Adjusting for 3-Month Renewals

If tasks need to be renewed every 3 months, adjust the formula to:

=SUMPRODUCT(--(C2:T2>=TODAY()-90)) / 19

This formula checks if each task date is within the last 3 months.

Conditional Formatting

To show when tasks are out of date or due for renewal, you can use conditional formatting:

1. Select the range C2:T2.
2. Go to the Home tab.
3. Click Conditional Formatting > New Rule.
4. Select Use a formula to determine which cells to format.
5. Enter the formula to highlight tasks that are out of date:

=C2<TODAY()-365

1. Set the format to highlight the cells (e.g., red fill color).
2. Repeat steps 3-6 for tasks due for renewal in 3 months:

=AND(C2>=TODAY()-365, C2<=TODAY()-90)

Example Table with Formulas

Here's an example table with formulas:

Make sure to drag the formula in column B down for all rows containing staff names.

By using these formulas, you can calculate the percentage of in-date tasks for each staff member and apply conditional formatting to visually identify tasks that are out of date or due for renewal.

AI was partially deployed to support the text and formulas.

AI can make mistakes. Consider checking important information.

All formulas are untested, it is always recommended to make a backup of the existing file in advance before using the above formulas.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.