Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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; Hi, Month GSM number Extension Group name Subgroup name Name GSM number User ref1 User Ref2 Call date Call time ...

  1. #1
    Board Regular
    Join Date
    Mar 2013
    Posts
    75

    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
    2,352

    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

    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

    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
    2,352

    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
    2,352

    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

    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

    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
    2,352

    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

    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.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com