Formula Help

[13Jkidd13]

Hi all,

Im very new to Excel and require some help on creating a formula. I need a formula to help with the below:

in the cll duration column i used =finish-start.

what i need to do is in a new tab-collate all calls dialled each day and have the um of all the minutes for this. so for example

5/12/20

2:55:00

 

[etaf]

you could use a pivot table to do the SUM
do you know pivot tables at all

otherwise
A SUMIF() would work on the new tab
I notice there are know entries for Friday 4th Dec in that example

 

[13Jkidd13]

i am literally a novice with excel. this is for work. i need something which will automatically work out the time spend each time he makes a call for each day.

 

[etaf]

How do you get the data for the spreadsheet
OR is this something that "HE" updates
A pivot table would be best, as you can hit refresh and it will update based on the data entered
you can setup a new sheet to extract the unique dates and then do a sumif()
do you have an example of you data you can either use XL2BB to post here OR put a link to a share like dropbox/onedrive
meanwhile i can setup a sample based on the image above

 

[13Jkidd13]

here is the file saved on googledrive

Nedelcu A6720EM.xlsx

drive.google.com

 

[etaf]

i have added a file to dropbox - link here, BUT just seen your sample and will look at that
I only leave the sample on dropbox for a month

Dropbox - File Deleted - Simplify your life

www.dropbox.com

And heres the XL2bb

Dates-Extract-Duration-ETAF.xlsx
	A	B	C	D
1	Date	Start	Stop	Duration
2	12/1/20	0:30	0:48	0:18
3	12/1/20	0:55	1:23	0:28
4	12/1/20	1:20	1:58	0:38
5	12/2/20	1:45	2:33	0:48
6	12/2/20	2:10	3:08	0:58
7	12/2/20	2:35	3:43	1:08
8	12/2/20	3:00	4:18	1:18
9	12/2/20	3:25	4:53	1:28
10	12/3/20	3:50	5:28	1:38
11	12/3/20	4:15	6:03	1:48
12	12/3/20	4:40	6:38	1:58
13	12/3/20	5:05	7:13	2:08
14	12/3/20	5:30	7:48	2:18
15	12/3/20	5:55	8:23	2:28
16	12/3/20	6:20	8:58	2:38
17	12/3/20	6:45	9:33	2:48
18	12/3/20	7:10	10:08	2:58
19	12/3/20	7:35	10:43	3:08
20	12/3/20	8:00	11:18	3:18
21	12/3/20	8:25	11:53	3:28
22	12/3/20	8:50	12:28	3:38
23	12/3/20	9:15	13:03	3:48
24	12/3/20	9:40	13:38	3:58
25	12/4/20	10:05	14:13	4:08
26	12/4/20	10:30	14:48	4:18
27	12/4/20	10:55	15:23	4:28
28	12/4/20	11:20	15:58	4:38
29	12/4/20	11:45	16:33	4:48
30	12/4/20	12:10	17:08	4:58
31	12/4/20	12:35	17:43	5:08
32	12/4/20	13:00	18:18	5:18
33	12/4/20	13:25	18:53	5:28
34	12/4/20	13:50	19:28	5:38
35	12/4/20	14:15	20:03	5:48
36	12/4/20	14:40	20:38	5:58
37	12/4/20	15:05	21:13	6:08
38	12/4/20	15:30	21:48	6:18
39	12/5/20	15:55	22:23	6:28
40	12/5/20	16:20	22:58	6:38
41	12/5/20	16:45	23:33	6:48
42	12/5/20	17:10	0:08	6:58
43	12/5/20	17:35	0:43	7:08
44	12/5/20	18:00	1:18	7:18
45	12/5/20	18:25	1:53	7:28
46	12/5/20	18:50	2:28	7:38
47	12/5/20	19:15	3:03	7:48
48	12/5/20	19:40	3:38	7:58
49	12/5/20	20:05	4:13	8:08
50	12/5/20	20:30	4:48	8:18
51	12/5/20	20:55	5:23	8:28
52	12/5/20	21:20	5:58	8:38
DATA

Cell Formulas
Range	Formula
D2:D52	D2	=C2-B2

SUMMARY SHEET

Dates-Extract-Duration-ETAF.xlsx
	A	B
1	Dates	Duration
2	12/1/20	1:24
3	12/2/20	5:40
4	12/3/20	42:00
5	12/4/20	73:02
6	12/5/20	105:42
7	1/0/00
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
Summary

Cell Formulas
Range	Formula
A2:A22	A2	=IFERROR(INDEX(DATA!$A$2:$A$10000, MATCH(0, INDEX(COUNTIF(Summary!$A$1:A1, DATA!$A$2:$A$10000), 0, 0), 0)), "")
B2:B22	B2	=IF(OR(A2=0,A2=""),"",SUMIF(DATA!A:A,Summary!A2,DATA!D:D))

PIVOT TABLE

Dates-Extract-Duration-ETAF.xlsx
	A	B
2
3	Row Labels	Sum of Duration
4	12/1/20	1:24
5	12/2/20	5:40
6	12/3/20	42:00
7	12/4/20	73:02
8	12/5/20	105:42
9	Grand Total	227:48
Pvt-Table

 

[etaf]

OK, Will you always have a call duration sheet with the dates already filled in

if so then a simple SUMIF()
=SUMIF('Pin Monitoring'!B15:B109,A4,'Pin Monitoring'!F15:F109)

Avoid merged cells as that makes formulas very difficult to use and manage

Nedelcu A6720EM.xlsx
	A	B	C	D
3	Date		Minutes monitored
4	12/1/20		0:30
5	12/2/20		2:59
6	12/3/20		0:05
7	12/4/20		0:00
8	12/5/20		2:55
9	12/6/20		0:00
10	12/7/20		0:00
11	12/8/20		0:00
12	12/9/20		0:00
13	12/10/20		0:00
14	12/11/20		0:00
15	12/12/20		0:00
16	12/13/20		0:00
17	12/14/20		0:00
18	12/15/20		0:00
19	12/16/20		0:00
20	12/17/20		0:00
21	12/18/20		0:00
22	12/19/20		0:00
23	12/20/20		0:00
Call Duration Log

Cell Formulas
Range	Formula
C4:C23	C4	=SUMIF('Pin Monitoring'!B15:B109,A4,'Pin Monitoring'!F15:F109)

 

[13Jkidd13]

OK, Will you always have a call duration sheet with the dates already filled in

if so then a simple SUMIF()
=SUMIF('Pin Monitoring'!B15:B109,A4,'Pin Monitoring'!F15:F109)

Avoid merged cells as that makes formulas very difficult to use and manage

Nedelcu A6720EM.xlsx
	A	B	C	D
3	Date		Minutes monitored
4	12/1/20		0:30
5	12/2/20		2:59
6	12/3/20		0:05
7	12/4/20		0:00
8	12/5/20		2:55
9	12/6/20		0:00
10	12/7/20		0:00
11	12/8/20		0:00
12	12/9/20		0:00
13	12/10/20		0:00
14	12/11/20		0:00
15	12/12/20		0:00
16	12/13/20		0:00
17	12/14/20		0:00
18	12/15/20		0:00
19	12/16/20		0:00
20	12/17/20		0:00
21	12/18/20		0:00
22	12/19/20		0:00
23	12/20/20		0:00
Call Duration Log

Cell Formulas
Range	Formula
C4:C23	C4	=SUMIF('Pin Monitoring'!B15:B109,A4,'Pin Monitoring'!F15:F109)

the dates will not always be on there as it is dependant on when monitoring is conducted on said individual. it ould be that they monitor 28 calls on the 5/12/20 and then nothing until 10/12/20. if this is the case i only need it to calculate the days that monitoring is recorded on the sheet

 

[etaf]

Sorry, re-Phrase - Will the "CALL DURATION LOG" sheet - always have the unique dates from the "PIN MONITORING" sheet, is this some form of template that is provided ?
How is it populated - I can give you a formula to copy into , BUT it will depend on how the sheet is populated ?

I have added the formula to extract the Dates in column A on the "CALL DURATION LOG" sheet , removed the merged cells and formated so the formula works OK - change that and it may not work.
Added a conditional format to keep the font white for a zero date

Then used the range 15 to 200 on the "PIN MONITORING" sheet, as i did not know how many entries you may have
if you then use this as a template - on the "PIN MONITORING" , they can add the date / start and finish time and the Call Duration will be calculated and the "CALL DURATION LOG": sheet automatically updates

To extract the dates into the "CALL DURATION LOG" I have used
=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF('Call Duration Log'!$A$1:A1, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")


And for the Duration on the "CALL DURATION LOG" I have used

=IF(OR(A2=0,A2=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,'Call Duration Log'!A2,'Pin Monitoring'!$F$15:$F$200))

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Nedelcu A6720EM (ETAF).xlsx
	A	B
1	Date	Minutes monitored
2	12/1/20	0:30
3	12/2/20	2:59
4	12/3/20	0:05
5	12/5/20	2:55
6	1/0/00
7
8
9
10
11
12
13
14
15
Call Duration Log

Cell Formulas
Range	Formula
A2:A15	A2	=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF('Call Duration Log'!$A$1:A1, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")
B2:B15	B2	=IF(OR(A2=0,A2=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,'Call Duration Log'!A2,'Pin Monitoring'!$F$15:$F$200))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=A1=0	text	NO

Nedelcu A6720EM (ETAF).xlsx
	B	C	D	E	F
13	Monitoring details or no calls
14	Date	Number dialed	Time call started	Time call
Pin Monitoring

 

[13Jkidd13]

Sorry, re-Phrase - Will the "CALL DURATION LOG" sheet - always have the unique dates from the "PIN MONITORING" sheet, is this some form of template that is provided ?
How is it populated - I can give you a formula to copy into , BUT it will depend on how the sheet is populated ?

I have added the formula to extract the Dates in column A on the "CALL DURATION LOG" sheet , removed the merged cells and formated so the formula works OK - change that and it may not work.
Added a conditional format to keep the font white for a zero date

Then used the range 15 to 200 on the "PIN MONITORING" sheet, as i did not know how many entries you may have
if you then use this as a template - on the "PIN MONITORING" , they can add the date / start and finish time and the Call Duration will be calculated and the "CALL DURATION LOG": sheet automatically updates

To extract the dates into the "CALL DURATION LOG" I have used
=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF('Call Duration Log'!$A$1:A1, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")


And for the Duration on the "CALL DURATION LOG" I have used

=IF(OR(A2=0,A2=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,'Call Duration Log'!A2,'Pin Monitoring'!$F$15:$F$200))

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Nedelcu A6720EM (ETAF).xlsx
	A	B
1	Date	Minutes monitored
2	12/1/20	0:30
3	12/2/20	2:59
4	12/3/20	0:05
5	12/5/20	2:55
6	1/0/00
7
8
9
10
11
12
13
14
15
Call Duration Log

Cell Formulas
Range	Formula
A2:A15	A2	=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF('Call Duration Log'!$A$1:A1, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")
B2:B15	B2	=IF(OR(A2=0,A2=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,'Call Duration Log'!A2,'Pin Monitoring'!$F$15:$F$200))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A:A	Expression	=A1=0	text	NO

Nedelcu A6720EM (ETAF).xlsx
	B	C	D	E	F
13	Monitoring details or no calls
14	Date	Number dialed	Time call started	Time call
Pin Monitoring

hello, i have been struggling with what you previously stated to input in the cells. i have uploaded a newer version of the monitoring template to my google drive, would you be able and willing to input the required formulas tooth's template for me?

Google Drive: Sign-in

Access Google Drive with a Google account (for personal use) or Google Workspace account (for business use).

drive.google.com