Forum Discussion
Non productive hours calculation
From the attached data, if any employee on leave then non productive hours should be blank or shoud not take into consideration and for rest sum of NPT hours to be calculated.
| Name | Code | Date | Times - Start Time | Times - Stop Time | Times - Duration | NPT Time |
| E | Meeting | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| E | SL | 5/1/2023 | 09:00 | 18:00 | 8:00 | 8 |
| E | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| B | Meeting | 5/1/2023 | 09:00 | 09:19 | 0:19 | 0.316667 |
| B | Meeting | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| B | PL | 5/2/2023 | 09:00 | 18:00 | 8:00 | 8 |
| B | Meeting | 5/2/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| H | SHIFT | 5/1/2023 | 09:00 | 18:00 | 9:00 | 9 |
| H | Training | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| H | PL | 5/2/2023 | 09:00 | 18:00 | 8:00 | 8 |
| I | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| K | SHIFT | 5/1/2023 | 09:00 | 18:00 | 9:00 | 9 |
| K | Training | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| K | CL | 5/3/2023 | 09:00 | 18:00 | 8:00 | 8 |
| K | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| F | Training | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| F | SL | 5/1/2023 | 09:00 | 18:00 | 8:00 | 8 |
| F | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| G | Training | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| G | CL | 5/1/2023 | 09:00 | 18:00 | 8:00 | 8 |
| G | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| J | Training | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| J | PL | 5/1/2023 | 09:00 | 18:00 | 8:00 | 8 |
| J | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| C | Meeting | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| C | CL | 5/3/2023 | 09:00 | 18:00 | 8:00 | 8 |
| C | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| I | Meeting | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| I | SL | 5/1/2023 | 09:00 | 18:00 | 8:00 | 8 |
| I | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| D | SHIFT | 5/1/2023 | 09:00 | 18:00 | 9:00 | 9 |
| D | Meeting | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| D | SL | 5/4/2023 | 09:00 | 18:00 | 8:00 | 8 |
| D | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
| A | Meeting | 5/1/2023 | 12:00 | 12:15 | 0:15 | 0.25 |
| A | SL | 5/1/2023 | 09:00 | 18:00 | 8:00 | 8 |
| A | Meeting | 5/1/2023 | 16:00 | 16:15 | 0:15 | 0.25 |
To calculate the sum of non-productive hours (NPT hours) for each employee in Excel, you can follow these steps:
- Open the Excel file and navigate to a new worksheet.
- Copy the provided data into columns A to F, starting from row 2. Make sure the headers are included.
- In cell G2, enter the following formula to check if the employee is on leave:
=IF(OR(B2="SL", B2="PL", B2="CL"), "", SUM(E2:F2))
- This formula checks if the code (column B) is "SL", "PL", or "CL". If it is, the cell will be blank. Otherwise, it will sum the values in columns E and F, which represent the NPT Time.
- Drag the formula in cell G2 down to apply it to the remaining rows.
- In cell H2, enter the following formula to calculate the sum of NPT hours for each employee:
=SUMIF(A:A, A2, G:G)
- This formula uses the SUMIF function to sum the NPT hours (column G) for each unique employee name (column A).
- Drag the formula in cell H2 down to apply it to the remaining rows.
Now, you should have the sum of NPT hours for each employee in column H. The cells corresponding to employees on leave will be blank.
For example:
IF "A" employee has three codes in May 1st, 2023, Meeting, Huddle and SL then all 3 NPT should be blank
IF "B" Employee has three codes in May 1. 2023.... Meeting 30 mins, Hudlle-20 mins and Training-10 mins
then the total NPT should be 1 hr
To calculate the total NPT (Non-Productive Time) for each employee in Excel, you can use the SUMIFS function to sum the durations based on multiple criteria. Here's an example formula you can use:
Assuming the data is in columns A to G, and the NPT Time is in column F, you can enter the following formula in cell H2:
=IF(AND(COUNTIFS($A$2:$A$33, $A2, $G$2:$G$33, "<>")>1, $F2=""), "", SUMIFS($F$2:$F$33, $A$2:$A$33, $A2, $G$2:$G$33, "<>")/60)
This formula checks if an employee has multiple codes on the same date and if the NPT Time is blank. If both conditions are met, it returns an empty string ("") to display a blank value. Otherwise, it sums the NPT Time (converted to hours by dividing by 60) for the employee using the SUMIFS function.
Copy the formula down for the remaining rows to calculate the total NPT for each employee.
Make sure to adjust the range references ($A$2:$A$33, $G$2:$G$33, $F$2:$F$33) in the formula to match the actual range of your data.
Note: The example assumes that the NPT Time is in the format of hours and minutes (e.g., 0:15 for 15 minutes). If your data is in a different format, you may need to adjust the formula accordingly.
Name Code Date Times - Start Time Times - Stop Time Times - Duration NPT Time Actual AHT E Meeting 5/1/2023 12:00 12:15 0:15 0.25 0.00 E SL 5/1/2023 09:00 18:00 8:00 8 0.00 E Meeting 5/1/2023 16:00 16:15 0:15 0.25 0.00 B Meeting 5/1/2023 09:00 09:19 0:19 0.316667 0.32 B Meeting 5/1/2023 12:00 12:15 0:15 0.25 0.25 B PL 5/2/2023 09:00 18:00 8:00 8 B Meeting 5/2/2023 16:00 16:15 0:15 0.25 H SHIFT 5/1/2023 09:00 18:00 9:00 9 H Training 5/1/2023 12:00 12:15 0:15 0.25 H PL 5/2/2023 09:00 18:00 8:00 8 I Meeting 5/1/2023 16:00 16:15 0:15 0.25 K SHIFT 5/1/2023 09:00 18:00 9:00 9 K Training 5/1/2023 12:00 12:15 0:15 0.25 K CL 5/3/2023 09:00 18:00 8:00 8 K Meeting 5/1/2023 16:00 16:15 0:15 0.25 F Training 5/1/2023 12:00 12:15 0:15 0.25 F SL 5/1/2023 09:00 18:00 8:00 8 F Meeting 5/1/2023 16:00 16:15 0:15 0.25 G Training 5/1/2023 12:00 12:15 0:15 0.25 G CL 5/1/2023 09:00 18:00 8:00 8 G Meeting 5/1/2023 16:00 16:15 0:15 0.25 J Training 5/1/2023 12:00 12:15 0:15 0.25 J PL 5/1/2023 09:00 18:00 8:00 8 J Meeting 5/1/2023 16:00 16:15 0:15 0.25 C Meeting 5/1/2023 12:00 12:15 0:15 0.25 C CL 5/3/2023 09:00 18:00 8:00 8 C Meeting 5/1/2023 16:00 16:15 0:15 0.25 I Meeting 5/1/2023 12:00 12:15 0:15 0.25 I SL 5/1/2023 09:00 18:00 8:00 8 I Meeting 5/1/2023 16:00 16:15 0:15 0.25 D SHIFT 5/1/2023 09:00 18:00 9:00 9 D Meeting 5/1/2023 12:00 12:15 0:15 0.25 D SL 5/4/2023 09:00 18:00 8:00 8 D Meeting 5/1/2023 16:00 16:15 0:15 0.25 A Meeting 5/1/2023 12:00 12:15 0:15 0.25 A SL 5/1/2023 09:00 18:00 8:00 8 A Meeting 5/1/2023 16:00 16:15 0:15 0.25
