Double Shifts Breakdown

[tezza]

Hi All,

I've looking into creating a sheet that breaks down double staffed / single staffed calls and was hoping someone has already created one that pretty much does what I need.

I've made a start but not getting the results I require.

As a care company, there are many clients that staff go to and some of the reports needed is to breakdown how many staff went there, but it needs to capture when two staff go to the call.

For example:

1 member of staff goes to a client on 20/07 for 6 hours

2 members of staff go to a client on the 21/07 at 14:00 for 4 hours each.

The sheet I'm working on will have columns something like:

Client 1 Duration Client 2 Duration

So for the above examples it would show:

Client 1...........Duration........................Client 2...........Duration................................Total Visits................Total Duration
....1....................6...............................................................................................1.............................6
....1....................4..............................1.....................4........................................2.............................8

Sorry about the dots, I'm not sure how to put in hard spaces.

The list can comprise of hundreds of rows.

Hope that makes sense, any suggestions to guide me are very welcome.

 

[tezza]

Here's a visual example, I'm looking for AU:BB to auto populate into another sheet

TimesheetExtract.csv
	A	B	C																				W	X	Y	Z																		AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1	Client Title	Client First Name	Client Last Name																				Date of Call	Call Start Time	Call End Time	Call Duration																				Client	Visit 1	Visit Hours in Decimal		Visit 2 (if duplicate client, date & start time)	Visit Hours in Decimal		Total Visits	Total Hours in Decimal
2	Mi	Co	Ph																				22/06/2023	15:45	19:15	03:30																				Mi Co Ph	6	18:00		4	12:00		10	30:00
3	Mi	Co	Ph																				22/06/2023	15:45	19:15	03:30																				Ma Da Ha	1	04:00		1	04:00		2	08:00
4	Mi	Co	Ph																				23/06/2023	15:45	18:15	02:30																				Ma Fr Ro	1	06:00		1	03:00		2	09:00
5	Mi	Co	Ph																				23/06/2023	15:45	18:15	02:30																				Mr Te Je	4	14:00					4	14:00
6	Mi	Co	Ph																				28/06/2023	15:45	19:15	03:30																					12	42:00		6	19:00		18	61:00
7	Mi	Co	Ph																				05/07/2023	15:45	19:15	03:30
8	Mi	Co	Ph																				05/07/2023	15:45	19:15	03:30
9	Mi	Co	Ph																				07/07/2023	15:45	18:15	02:30
10	Mi	Co	Ph																				14/07/2023	15:45	18:15	02:30
11	Mi	Co	Ph																				14/07/2023	15:45	18:15	02:30
12	Ma	Da	Ha																				25/06/2023	14:00	18:00	04:00
13	Ma	Da	Ha																				09/07/2023	14:00	18:00	04:00
14	Ma	Fr	Ro																				25/06/2023	10:00	16:00	06:00
15	Ma	Fr	Ro																				16/07/2023	13:00	16:00	03:00
16	Mr	Te	Je																				17/07/2023	15:45	19:15	03:30
17	Mr	Te	Je																				18/07/2023	14:00	18:00	04:00
18	Mr	Te	Je																				19/07/2023	15:45	19:15	03:30
19	Mr	Te	Je																				20/07/2023	13:00	16:00	03:00
20			18																							61:00
TimesheetExtract

Cell Formulas
Range	Formula
AV2	AV2	=Z2+Z4+Z6+Z7+Z9+Z10
AY2	AY2	=Z3+Z5+Z8+Z11
BA2	BA2	=COUNTA(C2:C11)
BB2	BB2	=SUM(Z2:Z11)
BA6:BB6,AX6:AY6,AU6:AV6	AU6	=SUM(AU2:AU5)
C20	C20	=COUNTA(C2:C19)
Z20	Z20	=SUM(Z2:Z19)

 

[tezza]

Is there anything else I need to clarify for support with this?

 

[Anonymous1378]

You can try this, but I'm not seeing the logic behind the second Ma Da Ha and Ma Fr Ro being "Visit 2" besides the color.

Book4
	A	B	C																				W	X	Y	Z	AA																			AT	AU	AV		AX	AY		BA	BB
1	Client Title	Client First Name	Client Last Name																				Date of Call	Call Start Time	Call End Time	Call Duration																				Client	Visit 1	Visit Hours in Decimal		Visit 2 (if duplicate client, date & start time)	Visit Hours in Decimal		Total Visits	Total Hours in Decimal
2	Mi	Co	Ph																				22-Jun-23	15:45	19:15	03:30	1																			Mi Co Ph	6	18:00		4	12:00		10	30:00
3	Mi	Co	Ph																				22-Jun-23	15:45	19:15	03:30	2																			Ma Da Ha	2	08:00		0	00:00		2	08:00
4	Mi	Co	Ph																				23-Jun-23	15:45	18:15	02:30	1																			Ma Fr Ro	2	09:00		0	00:00		2	09:00
5	Mi	Co	Ph																				23-Jun-23	15:45	18:15	02:30	2																			Mr Te Je	4	14:00		0	00:00		4	14:00
6	Mi	Co	Ph																				28-Jun-23	15:45	19:15	03:30	1																				14	49:00		4	12:00		18	61:00
7	Mi	Co	Ph																				05-Jul-23	15:45	19:15	03:30	1
8	Mi	Co	Ph																				05-Jul-23	15:45	19:15	03:30	2
9	Mi	Co	Ph																				07-Jul-23	15:45	18:15	02:30	1
10	Mi	Co	Ph																				14-Jul-23	15:45	18:15	02:30	1
11	Mi	Co	Ph																				14-Jul-23	15:45	18:15	02:30	2
12	Ma	Da	Ha																				25-Jun-23	14:00	18:00	04:00	1
13	Ma	Da	Ha																				09-Jul-23	14:00	18:00	04:00	1
14	Ma	Fr	Ro																				25-Jun-23	10:00	16:00	06:00	1
15	Ma	Fr	Ro																				16-Jul-23	13:00	16:00	03:00	1
16	Mr	Te	Je																				17-Jul-23	15:45	19:15	03:30	1
17	Mr	Te	Je																				18-Jul-23	14:00	18:00	04:00	1
18	Mr	Te	Je																				19-Jul-23	15:45	19:15	03:30	1
19	Mr	Te	Je																				20-Jul-23	13:00	16:00	03:00	1
20			18																							61:00
Sheet1

Cell Formulas
Range	Formula
AU2:AU5	AU2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=1),1,0))
AV2:AV5	AV2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=1),$Z$2:$Z$19,0))
AU6:AV6,BA6:BB6,AX6:AY6	AU6	=SUM(AU2:AU5)
AX2:AX5	AX2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=2),1,0))
AY2:AY5	AY2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=2),$Z$2:$Z$19,0))
BA2:BB5	BA2	=AU2+AX2
AA2:AA19	AA2	=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2,$W$2:$W2,W2,$X$2:$X2,X2)
C20	C20	=COUNTA(C2:C19)
Z20	Z20	=SUM(Z2:Z19)

 

[tezza]

You can try this, but I'm not seeing the logic behind the second Ma Da Ha and Ma Fr Ro being "Visit 2" besides the color.

Book4
	A	B	C																				W	X	Y	Z	AA																			AT	AU	AV		AX	AY		BA	BB
1	Client Title	Client First Name	Client Last Name																				Date of Call	Call Start Time	Call End Time	Call Duration																				Client	Visit 1	Visit Hours in Decimal		Visit 2 (if duplicate client, date & start time)	Visit Hours in Decimal		Total Visits	Total Hours in Decimal
2	Mi	Co	Ph																				22-Jun-23	15:45	19:15	03:30	1																			Mi Co Ph	6	18:00		4	12:00		10	30:00
3	Mi	Co	Ph																				22-Jun-23	15:45	19:15	03:30	2																			Ma Da Ha	2	08:00		0	00:00		2	08:00
4	Mi	Co	Ph																				23-Jun-23	15:45	18:15	02:30	1																			Ma Fr Ro	2	09:00		0	00:00		2	09:00
5	Mi	Co	Ph																				23-Jun-23	15:45	18:15	02:30	2																			Mr Te Je	4	14:00		0	00:00		4	14:00
6	Mi	Co	Ph																				28-Jun-23	15:45	19:15	03:30	1																				14	49:00		4	12:00		18	61:00
7	Mi	Co	Ph																				05-Jul-23	15:45	19:15	03:30	1
8	Mi	Co	Ph																				05-Jul-23	15:45	19:15	03:30	2
9	Mi	Co	Ph																				07-Jul-23	15:45	18:15	02:30	1
10	Mi	Co	Ph																				14-Jul-23	15:45	18:15	02:30	1
11	Mi	Co	Ph																				14-Jul-23	15:45	18:15	02:30	2
12	Ma	Da	Ha																				25-Jun-23	14:00	18:00	04:00	1
13	Ma	Da	Ha																				09-Jul-23	14:00	18:00	04:00	1
14	Ma	Fr	Ro																				25-Jun-23	10:00	16:00	06:00	1
15	Ma	Fr	Ro																				16-Jul-23	13:00	16:00	03:00	1
16	Mr	Te	Je																				17-Jul-23	15:45	19:15	03:30	1
17	Mr	Te	Je																				18-Jul-23	14:00	18:00	04:00	1
18	Mr	Te	Je																				19-Jul-23	15:45	19:15	03:30	1
19	Mr	Te	Je																				20-Jul-23	13:00	16:00	03:00	1
20			18																							61:00
Sheet1

Cell Formulas
Range	Formula
AU2:AU5	AU2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=1),1,0))
AV2:AV5	AV2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=1),$Z$2:$Z$19,0))
AU6:AV6,BA6:BB6,AX6:AY6	AU6	=SUM(AU2:AU5)
AX2:AX5	AX2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=2),1,0))
AY2:AY5	AY2	=SUM(IF(($A$2:$A$19&" "&$B$2:$B$19&" "&$C$2:$C$19=AT2)*($AA$2:$AA$19=2),$Z$2:$Z$19,0))
BA2:BB5	BA2	=AU2+AX2
AA2:AA19	AA2	=COUNTIFS($A$2:$A2,A2,$B$2:$B2,B2,$C$2:$C2,C2,$W$2:$W2,W2,$X$2:$X2,X2)
C20	C20	=COUNTA(C2:C19)
Z20	Z20	=SUM(Z2:Z19)

Apologies for the late reply, been busy.

I've just run this and it works really well, thank you