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))
www.dropbox.com
Nedelcu A6720EM (ETAF).xlsx |
---|
|
---|
| B | C | D | E | F |
---|
13 | Monitoring details or no calls |
---|
14 | Date | Number dialed | Time call started | Time call |
---|
|
---|