Excel Function to find actual usage for Triband 850NU plan

Hello Friends

I am the unfortunate soul who is thrown in the wild “alcatel” servers. :-)

MTNL Web Portal does not give me exact value of my usage and add the night surfing usage in my “ Accounted Volume”

Big wise man says download CSV and maintain an excel sheet to identify the actual usage. But it’s a headache, so I have written a function which will help me get the actual usage.

My programming skills are RIP with DODO, so please excuse me if you don’t find it useful.

So here are the steps to make ur life bit easy.

Step 1 : “Bolo MTNL Triband ki Jai”
Step 2 : Repeat step one 5 times.
Step 3 : Login to MTNL Web Self Care.
Step 4 : Select following options
View Type : Details
Select : Date Wise
Step 5 : Hit “Download as csv”
Step 6 : Store the csv (CDREntries.csv)
Step 7 : Open the CSV in excel.
Step 8 : Click M2, press = and copy formula
IF(((TIMEVALUE(MID(B2,13,9)) + TIMEVALUE(MID(C2,13,9)))<(TIMEVALUE("08:00:00")*2)),0,1)*K2
(For all you geek friends, the formula is simple,do write me if want explaination)
Press Enter
If the usage is inbetween happ hours (between 12 to 8), M2 will become zero else It will show the actual usage.

Step 9 : Copy the formula from cell M2 and paste it in remaining M$ column
( You can click Shift+Ctrl+End to select the column)

Step 10 : Hurrah , you got all the actual usage.
Step 11: Now you can write the sum function or just select the entire M Column to see the sum of usage on the footer bar.
Step 12: Say thanks to me :-)

Disclaimer : This function is written just to make our life better , with no commercial purpose. Its release under GNU public license, and I am not responsible if anything goes wrong with it or the data is inaccurate.
This information CANNOT be used to sue MTNL in court of law. :-)
MTNL Rocks!!!!!!!!


(I will try to modify the code and make it a one click operation. So keep visiting this place...)