Count if and sum if working days and working hours macro

[tuytuy]

Hi,
[TABLE="width: 2293"]
<colgroup><col><col><col><col span="2"><col><col span="2"><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Month[/TD]
[TD]GSM number[/TD]
[TD]Extension[/TD]
[TD]Group name[/TD]
[TD]Subgroup name[/TD]
[TD]Name GSM number[/TD]
[TD]User ref1[/TD]
[TD]User Ref2[/TD]
[TD]Call date[/TD]
[TD]Call time[/TD]
[TD]Call duration[/TD]
[TD]Destination[/TD]
[TD]Zone/Country/Operator[/TD]
[TD]Tariff[/TD]
[TD]Type[/TD]
[TD]Supplementary services[/TD]
[TD]Indicative tariff[/TD]
[TD]Usage amount[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-13[/TD]
[TD]0470/132056[/TD]
[TD][/TD]
[TD]Unfiled - Niet toegekend - Non attribué[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]19-Dec-12[/TD]
[TD="align: right"]10:51:51[/TD]
[TD="align: right"]00:00:42[/TD]
[TD="align: right"]31624298877[/TD]
[TD]Vodafone P-B[/TD]
[TD]Normal[/TD]
[TD]Outgoing roaming calls[/TD]
[TD]R[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.203[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-13[/TD]
[TD]0470/132056[/TD]
[TD][/TD]
[TD]Unfiled - Niet toegekend - Non attribué[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Dec-12[/TD]
[TD="align: right"]13:10:45[/TD]
[TD="align: right"]00:00:45[/TD]
[TD="align: right"]31624298877[/TD]
[TD]Vodafone P-B[/TD]
[TD]Réduit[/TD]
[TD]Outgoing roaming calls[/TD]
[TD]R[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.2175[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-13[/TD]
[TD]0470/132056[/TD]
[TD][/TD]
[TD]Unfiled - Niet toegekend - Non attribué[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Dec-12[/TD]
[TD="align: right"]14:16:36[/TD]
[TD="align: right"]00:16:15[/TD]
[TD="align: right"]31651275403[/TD]
[TD]Vodafone P-B[/TD]
[TD]Réduit[/TD]
[TD]Outgoing roaming calls[/TD]
[TD]R[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4.7122[/TD]
[/TR]
[TR]
[TD="align: right"]Jan-13[/TD]
[TD]0470/132056[/TD]
[TD][/TD]
[TD]Unfiled - Niet toegekend - Non attribué[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]29-Dec-12[/TD]
[TD="align: right"]13:27:07[/TD]
[TD="align: right"]00:00:30[/TD]
[TD="align: right"]31651275403[/TD]
[TD]Vodafone P-B[/TD]
[TD]Réduit[/TD]
[TD]Outgoing roaming calls[/TD]
[TD]R[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.145[/TD]
[/TR]
</tbody>[/TABLE]
i have a table like above,
i need, using a macro, to count and then sum the total amount used for all non working days and calls outside working hours (7.00 am to 7.00 pm / 0700 to 1900)
using an excel formula i was thinking of using the D formula on the date and then count only the ones that have a value equal to 6 or 7.
But i'm quite new to vba and i have no idea how to right a vb code to solve this.

 

[oldbrewer]

But my 2 tables will update automatically as the weeks go by. Each week 2 numbers will present, number of calls and cost of calls. Do you need more information than this?

 

[tuytuy]

the thing is that i copied only a very smell bit of the table i get, the table is about 800000 lines long, and contains way more columns and sheets then that. The bit that i pasted on the msg is only an small portion of it, it really needs to be a vba code there is no other way.

 

[oldbrewer]

ok - I don't think I am able to help further on this without seeing the whole table and a very clear statement of the information you wish to extract. Before I retired I analysed, in real time , a spreadsheet with 90 columns and around 4000 rows per year, each year being on a separate sheet. This was done without macros, and totally automatically.

 

[tuytuy]

can you send me by pm your email adress and i can send a extact of the table, i would send the complete one but it contains sensible informations and is 145 mo big...

 

[oldbrewer]

I have already sent you an Email - get my address from there - it is ********@zen.co.uk

Change the names and the numbers for security purposes eg tom **** fred 33 44 55