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]

It is totally unclear what you want to do. For example, what is the amount used?

 

[tuytuy]

usage amount its the last column of the table.

 

[tuytuy]

i just want to count the number of communication during the weekend and outside working hours.
each record on the table is one communication.

 

[oldbrewer]

sorry - I did not realise the table scrolled.

 

[oldbrewer]

I have now got a working spreadsheet PM me with your email address and I will send it.

 

[tuytuy]

done thank you so much i'll try it right know

 

[tuytuy]

hmm that's not quit what i was looking for.
I was trying to create a MACRO so a vba code to count the number of call outside the working hours and the weekend. So basically i just need a code that will output only a number.
And then i would need a separate one that would calculate the amount used for these calls.
In the files you've send me, there is no vba code but just excel functions

 

[oldbrewer]

ok - but if the number of calls pre 0700 and post 1900 and at weekend is all you need ( and corresponding amount used) then I can make those 2 tables less detailed. Why do you need a macro?

A modified table will break down the calls by day or week or month whatever you want.

 

[tuytuy]

because it will be part of a way longer macro, i need to do this in a new file every week, so using a vba code makes it way easier to do.