Non productive hours calculation

[reshambahadur]

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

[#2]

@reshambahadur 

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

[#3]

@reshambahadur 

To calculate the sum of non-productive hours (NPT hours) for each employee in Excel, you can follow these steps:

1. Open the Excel file and navigate to a new worksheet.
2. Copy the provided data into columns A to F, starting from row 2. Make sure the headers are included.
3. 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))

1. 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.
2. Drag the formula in cell G2 down to apply it to the remaining rows.
3. In cell H2, enter the following formula to calculate the sum of NPT hours for each employee:

=SUMIF(A:A, A2, G:G)

1. This formula uses the SUMIF function to sum the NPT hours (column G) for each unique employee name (column A).
2. 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.

[#4]

@NikolinoDE 

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 

[#5]

@reshambahadur 

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.