drewmorris
New Member
- Joined
- Oct 2, 2018
- Messages
- 2
Hi and thank you for all help in advance,
I have a csv. file that I download and it gives me the data like this (not real data):
A B C D
[TABLE="width: 399"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CallDate[/TD]
[TD]DialledNumber[/TD]
[TD]CallingNumber[/TD]
[TD]CallDuration[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2018 10:34[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07333333333[/TD]
[TD="align: right"]00:04:47[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018 12:22[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07222222222[/TD]
[TD="align: right"]00:06:04[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018 11:50[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07111111111[/TD]
[TD="align: right"]00:06:13[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2018 16:01[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07444444444[/TD]
[TD="align: right"]00:04:06[/TD]
[/TR]
[TR]
[TD="align: right"]17/09/2018 12:43[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07555555555[/TD]
[TD="align: right"]00:19:59[/TD]
[/TR]
</tbody>[/TABLE]
If possible, I would like a couple of Formulas which will give me the following:
[TABLE="width: 468"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD]Longest Call [/TD]
[TD]Longest Caller Number[/TD]
[TD]Longest Call Date[/TD]
[/TR]
[TR]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]00:00:00
[/TD]
[TD]XXXXX XXX XXX[/TD]
[TD]XX/XX/XX
[/TD]
[/TR]
</tbody>[/TABLE]
01999999999
For the 'Longest Call' I have tried using the following formula: {=MAX(IF(criteria_range=criteria,value_range))} but it has been giving me '00:00:00' even when there is data.
In summary, I would like a formula for extracting the longest call, longest call number and the date the call was done.
Again thank you for all help.
I have a csv. file that I download and it gives me the data like this (not real data):
A B C D
[TABLE="width: 399"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]CallDate[/TD]
[TD]DialledNumber[/TD]
[TD]CallingNumber[/TD]
[TD]CallDuration[/TD]
[/TR]
[TR]
[TD="align: right"]27/09/2018 10:34[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07333333333[/TD]
[TD="align: right"]00:04:47[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018 12:22[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07222222222[/TD]
[TD="align: right"]00:06:04[/TD]
[/TR]
[TR]
[TD="align: right"]26/09/2018 11:50[/TD]
[TD="align: right"]01999999999[/TD]
[TD="align: right"]07111111111[/TD]
[TD="align: right"]00:06:13[/TD]
[/TR]
[TR]
[TD="align: right"]19/09/2018 16:01[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07444444444[/TD]
[TD="align: right"]00:04:06[/TD]
[/TR]
[TR]
[TD="align: right"]17/09/2018 12:43[/TD]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]07555555555[/TD]
[TD="align: right"]00:19:59[/TD]
[/TR]
</tbody>[/TABLE]
If possible, I would like a couple of Formulas which will give me the following:
[TABLE="width: 468"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]Number[/TD]
[TD]Longest Call [/TD]
[TD]Longest Caller Number[/TD]
[TD]Longest Call Date[/TD]
[/TR]
[TR]
[TD="align: right"]01888888888[/TD]
[TD="align: right"]00:00:00
[/TD]
[TD]XXXXX XXX XXX[/TD]
[TD]XX/XX/XX
[/TD]
[/TR]
</tbody>[/TABLE]
01999999999
For the 'Longest Call' I have tried using the following formula: {=MAX(IF(criteria_range=criteria,value_range))} but it has been giving me '00:00:00' even when there is data.
In summary, I would like a formula for extracting the longest call, longest call number and the date the call was done.
Again thank you for all help.