# Count if and sum if working days and working hours macro

This is a discussion on Count if and sum if working days and working hours macro within the Excel Questions forums, part of the Question Forums category; ...

1. ## Count if and sum if working days and working hours macro

Hi,
 Month GSM number Extension Group name Subgroup name Name GSM number User ref1 User Ref2 Call date Call time Call duration Destination Zone/Country/Operator Tariff Type Supplementary services Indicative tariff Usage amount Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 19-Dec-12 10:51:51 00:00:42 31624298877 Vodafone P-B Normal Outgoing roaming calls R 0 0.203 Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 29-Dec-12 13:10:45 00:00:45 31624298877 Vodafone P-B Réduit Outgoing roaming calls R 0 0.2175 Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 29-Dec-12 14:16:36 00:16:15 31651275403 Vodafone P-B Réduit Outgoing roaming calls R 0 4.7122 Jan-13 0470/132056 Unfiled - Niet toegekend - Non attribué 29-Dec-12 13:27:07 00:00:30 31651275403 Vodafone P-B Réduit Outgoing roaming calls R 0 0.145
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.

2. ## Re: Count if and sum if working days and working hours macro

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

3. ## Re: Count if and sum if working days and working hours macro

usage amount its the last column of the table.

4. ## Re: Count if and sum if working days and working hours macro

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

5. ## Re: Count if and sum if working days and working hours macro

sorry - I did not realise the table scrolled.

7. ## Re: Count if and sum if working days and working hours macro

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

8. ## Re: Count if and sum if working days and working hours macro

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

9. ## Re: Count if and sum if working days and working hours macro

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.

10. ## Re: Count if and sum if working days and working hours macro

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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•