CDR calculation

[rajeshi1980]

Hello,

Need help in calculating Call Detail Record of few mobile numbers.

Have already calculated all new rates as per new tariff, but am not able to deduct free minutes from the local charges.

basically in the raw cdr, with values, i want to make $0 to the top 500 minutes of local calls.
Can someone help me with this...

 

[ztodd]

	A	B	C	D	E	F	G	H	I
1	dt	tm	orig	dest	typ	dur	loc cum dur	adj dur	amt
2	02/12/2013	08:30:00	334455323	7738737	Local Call	0:02:00	0:02:00	0:00:00	0
3	02/12/2013	08:40:00	334455323	9992222222	ILD	0:00:32	0:02:00	0:00:32	18.2
4	02/12/2013	08:44:00	334455323	3362733	Local Call	0:07:00	0:09:00	0:00:00	0
5	02/12/2013	08:49:00	334455323	3362733	Local Call	0:48:00	0:57:00	0:47:00	5.2
6	02/13/2013	08:30:00	334455323	7738737	Local Call	0:02:00	0:59:00	0:02:00	1.2
7	02/13/2013	08:40:00	334455323	9992222222	ILD	0:00:32	0:59:00	0:00:32	18.2
8	02/13/2013	08:44:00	334455323	3362733	Local Call	1:12:00	2:11:00	1:12:00	9.8
9	02/13/2013	08:49:00	334455323	3362733	Local Call	0:48:00	2:59:00	0:48:00	5.2

<tbody>
</tbody>

Using the 2nd method- I also added a "helper" column to show cumulative local call duration in column G.

Make your sheet look like the table above.
formula in G2: =SUMIF(F$2:F2,"Local Call",G$2:G2)
formula in H2: =IF(F2="Local Call",IF(H2<10/60/24,0,IF(H2-G2<10/60/24,H2-10/60/24,G2)),G2)

Fill down column G and H.
Then change your formula in column I to be based on column H instead of F.