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 need a share like dropbox, no access available to that link

 

[13Jkidd13]

you need a share like dropbox, no access available to that link

I've just enabled access for yourself

 

[13Jkidd13]

Pin Monitoring Log Template.xlsx

Shared with Dropbox

www.dropbox.com

 

[etaf]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

The link will only be live for about a MONTH - hence why I have also included the XL2BB info, so others can follow , even if the spreadsheet on the shares are nolonger live


I have added the 2 formulas in the summary sheet to extract all the UNIQUE dates using
=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF(Summary!$B$2:B2, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")
I have also then added the Total
=IF(OR(B3=0,B3=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,Summary!B3,'Pin Monitoring'!$G$15:$G$200))
Then I have formatted the C duration column to [HH]:MM so it will report over 24hrs , should this happen and not return to zero after 24 hrs
because you have coloured banding and the last entry in the extraction will be a zero date 1/1/1900
I have added 2 conditional formats for font
One White and one light blue to match the background
Using
=AND(B3=0,ISODD(ROW(B3))) font Light Blue
and
=AND(B3=0,ISEVEN(ROW(B3))) Font White

I also extended the formatting on the data input PIN sheet to row 200 - DOES that cover the total possible amount of data input ? as you see above the ranges goto $200

Summary sheet here

Pin Monitoring Log Template -ETAF.xlsx
	B	C
2	Date	Duration
3	12/5/20	10:17
4	1/5/20	08:38
5	12/13/20	17:24
6	12/6/20	35:00
7	1/0/00
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Summary

Cell Formulas
Range	Formula
B3:B33	B3	=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF(Summary!$B$2:B2, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")
C3:C33	C3	=IF(OR(B3=0,B3=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,Summary!B3,'Pin Monitoring'!$G$15:$G$200))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B33	Expression	=AND(B3=0,ISODD(ROW(B3)))	text	NO
B3:B33	Expression	=AND(B3=0,ISEVEN(ROW(B3)))	text	NO

Data Sheet

Pin Monitoring Log Template -ETAF.xlsx
	B	C	D	E	F	G	H	I	J
13	Monitoring details or no calls						Actions taken		Staff name
14	Todays Date	Date monitored	Number dialed	Call started	Call ended	Call duration	Contents of call	IR (Y/N) Call tagged	Print
15	12/5/20	12/1/20	test	10:00	11:49	1:49	test	test	test
16	12/5/20	12/2/20	test	20:55	5:23	8:28	test	test	test
17	1/5/20	12/3/20	test	21:20	5:58	8:38	test	test	test
18	12/13/20	12/4/20	test	7:10	10:08	2:58	test	test	test
19	12/13/20	12/5/20	test	17:35	0:43	7:08	test	test	test
20	12/13/20	12/5/20	test	18:00	1:18	7:18	test	test	test
21	12/6/20			3:00	20:00	17:00
22	12/6/20			4:00	22:00	18:00
23						0:00
24						0:00
25						0:00
26						0:00
27						0:00
28						0:00
29						0:00
30						0:00
31						0:00
32						0:00
33						0:00
34						0:00
35						0:00
36						0:00
37						0:00
38						0:00
39						0:00
40						0:00
41						0:00
42						0:00
43						0:00
44						0:00
45						0:00
46						0:00
47						0:00
48						0:00
49						0:00
50						0:00
51						0:00
52						0:00
53						0:00
54						0:00
55						0:00
56						0:00
57						0:00
58						0:00
59						0:00
60						0:00
61						0:00
62						0:00
63						0:00
64						0:00
65						0:00
66						0:00
Pin Monitoring

 

[13Jkidd13]

Dropbox - File Deleted - Simplify your life

www.dropbox.com

The link will only be live for about a MONTH - hence why I have also included the XL2BB info, so others can follow , even if the spreadsheet on the shares are nolonger live


I have added the 2 formulas in the summary sheet to extract all the UNIQUE dates using
=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF(Summary!$B$2:B2, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")
I have also then added the Total
=IF(OR(B3=0,B3=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,Summary!B3,'Pin Monitoring'!$G$15:$G$200))
Then I have formatted the C duration column to [HH]:MM so it will report over 24hrs , should this happen and not return to zero after 24 hrs
because you have coloured banding and the last entry in the extraction will be a zero date 1/1/1900
I have added 2 conditional formats for font
One White and one light blue to match the background
Using
=AND(B3=0,ISODD(ROW(B3))) font Light Blue
and
=AND(B3=0,ISEVEN(ROW(B3))) Font White

I also extended the formatting on the data input PIN sheet to row 200 - DOES that cover the total possible amount of data input ? as you see above the ranges goto $200

Summary sheet here

Pin Monitoring Log Template -ETAF.xlsx
	B	C
2	Date	Duration
3	12/5/20	10:17
4	1/5/20	08:38
5	12/13/20	17:24
6	12/6/20	35:00
7	1/0/00
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Summary

Cell Formulas
Range	Formula
B3:B33	B3	=IFERROR(INDEX('Pin Monitoring'!$B$15:$B$200, MATCH(0, INDEX(COUNTIF(Summary!$B$2:B2, 'Pin Monitoring'!$B$15:$B$200), 0, 0), 0)), "")
C3:C33	C3	=IF(OR(B3=0,B3=""),"",SUMIF('Pin Monitoring'!$B$15:$B$200,Summary!B3,'Pin Monitoring'!$G$15:$G$200))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:B33	Expression	=AND(B3=0,ISODD(ROW(B3)))	text	NO
B3:B33	Expression	=AND(B3=0,ISEVEN(ROW(B3)))	text	NO

Data Sheet

Pin Monitoring Log Template -ETAF.xlsx
	B	C	D	E	F	G	H	I	J
13	Monitoring details or no calls						Actions taken		Staff name
14	Todays Date	Date monitored	Number dialed	Call started	Call ended	Call duration	Contents of call	IR (Y/N) Call tagged	Print
15	12/5/20	12/1/20	test	10:00	11:49	1:49	test	test	test
16	12/5/20	12/2/20	test	20:55	5:23	8:28	test	test	test
17	1/5/20	12/3/20	test	21:20	5:58	8:38	test	test	test
18	12/13/20	12/4/20	test	7:10	10:08	2:58	test	test	test
19	12/13/20	12/5/20	test	17:35	0:43	7:08	test	test	test
20	12/13/20	12/5/20	test	18:00	1:18	7:18	test	test	test
21	12/6/20			3:00	20:00	17:00
22	12/6/20			4:00	22:00	18:00
23						0:00
24						0:00
25						0:00
26						0:00
27						0:00
28						0:00
29						0:00
30						0:00
31						0:00
32						0:00
33						0:00
34						0:00
35						0:00
36						0:00
37						0:00
38						0:00
39						0:00
40						0:00
41						0:00
42						0:00
43						0:00
44						0:00
45						0:00
46						0:00
47						0:00
48						0:00
49						0:00
50						0:00
51						0:00
52						0:00
53						0:00
54						0:00
55						0:00
56						0:00
57						0:00
58						0:00
59						0:00
60						0:00
61						0:00
62						0:00
63						0:00
64						0:00
65						0:00
66						0:00
Pin Monitoring

Absolutely spot on. this has helped massively and cleared and helped me gain some knowledge into how the formulas are build. I appreciate you taking your time to help myself out and to provide advice in a clear and instructional manor.

Can i ask where you learnt your excel skills and knowledge. it is something i would benefit in learning.

 

[etaf]

Can i ask where you learnt your excel skills and knowledge. it is something i would benefit in learning.

I learnt an inhouse spreadsheet program on a mainframe in the early 80's , then lotus123 and eventually Excel.
Formal training courses in the very early days, but need to use for the business. then providing workshops for senior managers in the late90's early 00's
Working as a Information management professional for many years.
Also consulting before retiring and using Excel for dashboards & KPI's for performance Improvement & change
Keeping my hand in now while retired answering and helping on forums like this. And using online help and various courses on line. Reading answers to questions here and looking into all the alternative solutions that are often posted.
So I review the questions with no answers most days and then subscribe to some i'm interested in finding out how others solve those problems for people

 

[13Jkidd13]

I learnt an inhouse spreadsheet program on a mainframe in the early 80's , then lotus123 and eventually Excel.
Formal training courses in the very early days, but need to use for the business. then providing workshops for senior managers in the late90's early 00's
Working as a Information management professional for many years.
Also consulting before retiring and using Excel for dashboards & KPI's for performance Improvement & change
Keeping my hand in now while retired answering and helping on forums like this. And using online help and various courses on line. Reading answers to questions here and looking into all the alternative solutions that are often posted.
So I review the questions with no answers most days and then subscribe to some i'm interested in finding out how others solve those problems for people

Wow, that's amazing. so your knowledge is vast ! as a novice where would you suggest i start? i can do some basic formula bits and bobs but with the role i have just taken up i need to increase my knowledge and skill to a much much higher level.

 

[etaf]

depends on how you learn , Youtube videos, general searches , or Books, quite a lot of good books used to be available in the 90's 00's on excel
whats the role ?

 

[13Jkidd13]

depends on how you learn , Youtube videos, general searches , or Books, quite a lot of good books used to be available in the 90's 00's on excel
whats the role ?

so i actually work for HM prison service as the performance lead. in terms of data collation i find that easy, however im looking at streamlining the process around the spreadsheets that are used by various area.such as the one that you have assisted with.

in terms of learning im more of a hands on learner so watching videos would be a great way. I've tried watching a few but need to get my head around what each thing does exactly and not run before i can walk

 

[etaf]

there are now quite a few free online video courses , where you dont need to signup and all free (at least they where a few years ago , when i tried a few.
I think pivot tables you may find useful and i suspects graphs, dashboard etc How to organise data for analysis etc
Search youtube for free excel - there are loads, and then its a case of finding a presenter you like.
It maybe that the service would find some formal online training for you.

Theres a large list of resources here on mrexcel - see this link, Free on-line tutorial series from beginner to advanced it is from 2017 , so some of the links may nolonger work - but looks like an extensive list

Mr Excel has a youtube channel

https://www.youtube.com/channel/UCXbicpVq_ALWG4ijPKsR7ZQ

Then also post your questions here and as well as getting the solution, have a look at the functions and see if you can breakdown how they work

The fx icon next to the formula bar is also a very good help where it will give info on a function, I often find that useful and then use a search online if i'm not sure about the microsoft help fx

you may find you would benefit using VBA , I streamlined a load of processes and using data pulled together information into a dashboard using a lot of VBA in the late 90's