Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Count if and sum if working days and working hours macro

  1. #1
    Board Regular
    Join Date
    Mar 2013
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    Board Regular
    Join Date
    Mar 2013
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if and sum if working days and working hours macro

    usage amount its the last column of the table.

  4. #4
    Board Regular
    Join Date
    Mar 2013
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if and sum if working days and working hours macro

    sorry - I did not realise the table scrolled.

  6. #6
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if and sum if working days and working hours macro

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

  7. #7
    Board Regular
    Join Date
    Mar 2013
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Count if and sum if working days and working hours macro

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

  8. #8
    Board Regular
    Join Date
    Mar 2013
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #9
    Board Regular
    Join Date
    Apr 2010
    Posts
    9,192
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Mar 2013
    Posts
    75
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
This website uses cookies
We use cookies to store session information to facilitate remembering your login information, to allow you to save website preferences, to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners.
     


DMCA.com