Calculating complete hours over target

[Marmaduke0703]

Hi,

I have a report I need to pull some summary data from but I am unable to edit the report itself so no extra columns for me unfortunately! – shame as it would be quite simple if I could…

The report contains information around task completion times and we have a target of 90 minutes to complete the task before a penalty charge is added. For each complete hour after the initial 90 minutes, an additional charge is added.

What I need to do is calculate the total number of complete hours over the initial 90 minutes per day – note: this is complete hours per incident, not summing up part hours over the day.

I’ve attached a simplified worksheet to try and help explain…
Columns A & B contain simulated information available that I am unable to edit.
Column J contains the final figure I’m trying to reach

Columns D&E are the breakdown of how I’d get there if I could add an extra column, colour coded to the final table (column J).

Any idea how I can get straight from the raw data to the figures in column J without the step in column E?

Thanks

Raw Data tab…						Summary tab…
Col A	Col B		Col D	Col E					Col J
Date	Completion time (minutes)		Workings (for illustration only, not in Raw Data tab)			Date	Total Incidents	Incidents over 90 minutes	Complete hours over 90 minutes
01/07/2021​	65​			0		01/07/2021​	5	2	4
01/07/2021​	90​			0		02/07/2021​	3	1	1
01/07/2021​	120​			0		03/07/2021​	4	2	3
01/07/2021​	160​		=Rounddown(160-90)/60	1
01/07/2021​	270​		=Rounddown(270-90)/60	3
02/07/2021​	50​			0
02/07/2021​	80​			0
02/07/2021​	150​		=Rounddown(150-90)/60	1
03/07/2021​	180​		=Rounddown(180-90)/60	1
03/07/2021​	215​		=Rounddown(215-90)/60	2
03/07/2021​	85​			0
03/07/2021​	90​			0

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Raw Data tab…						Summary tab…
2	Col A	Col B		Col D	Col E					Col J
3	Date	Completion time (minutes)		Workings (for illustration only, not in Raw Data tab)			Date	Total Incidents	Incidents over 90 minutes	Complete hours over 90 minutes
4	01/07/2021	65			0		01/07/2021	5	3	4
5	01/07/2021	90			0		02/07/2021	3	1	1
6	01/07/2021	120			0		03/07/2021	4	2	3
7	01/07/2021	160			1
8	01/07/2021	270			3
9	02/07/2021	50			0
10	02/07/2021	80			0
11	02/07/2021	150			1
12	03/07/2021	180			1
13	03/07/2021	215			2
14	03/07/2021	85			0
15	03/07/2021	90			0
16
Master

Cell Formulas
Range	Formula
G4:G6	G4	=UNIQUE(A4:A15)
H4:H6	H4	=COUNTIFS(A:A,G4#)
I4:I6	I4	=COUNTIFS(A:A,G4#,B:B,">90")
J4:J6	J4	=SUM(ROUNDDOWN((FILTER($B$4:$B$15,($A$4:$A$15=G4)*($B$4:$B$15>90))-90)/60,0))
Dynamic array formulas.

 

[SueBristow]

Date range I've called IncidentDate
Completion range I've called Completion_time
The date for the summary of the day's incidents is in column M

=sumproduct((IncidentDate=M2)*((Completion_time>90)*((Rounddown((Completion_time-90)/60,0)))))

Mum knows the answers to everything ?

 

[Marmaduke0703]

Date range I've called IncidentDate
Completion range I've called Completion_time
The date for the summary of the day's incidents is in column M

=sumproduct((IncidentDate=M2)*((Completion_time>90)*((Rounddown((Completion_time-90)/60,0)))))

Mum knows the answers to everything ?

You found your password then

 

[Marmaduke0703]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Raw Data tab…						Summary tab…
2	Col A	Col B		Col D	Col E					Col J
3	Date	Completion time (minutes)		Workings (for illustration only, not in Raw Data tab)			Date	Total Incidents	Incidents over 90 minutes	Complete hours over 90 minutes
4	01/07/2021	65			0		01/07/2021	5	3	4
5	01/07/2021	90			0		02/07/2021	3	1	1
6	01/07/2021	120			0		03/07/2021	4	2	3
7	01/07/2021	160			1
8	01/07/2021	270			3
9	02/07/2021	50			0
10	02/07/2021	80			0
11	02/07/2021	150			1
12	03/07/2021	180			1
13	03/07/2021	215			2
14	03/07/2021	85			0
15	03/07/2021	90			0
16
Master

Cell Formulas
Range	Formula
G4:G6	G4	=UNIQUE(A4:A15)
H4:H6	H4	=COUNTIFS(A:A,G4#)
I4:I6	I4	=COUNTIFS(A:A,G4#,B:B,">90")
J4:J6	J4	=SUM(ROUNDDOWN((FILTER($B$4:$B$15,($A$4:$A$15=G4)*($B$4:$B$15>90))-90)/60,0))
Dynamic array formulas.

Ah yes! this works too!

I need to put an error trap in for anything that results in 0 but I can handle that thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.