Forum Discussion
Non productive hours calculation
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.