Results 1 to 10 of 10

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

  1. #1
    Seeker tuytuy's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    5
    Articles
    0

    Count if and sum if working days and working hours macro



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 (last column of the table) 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
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Tuytuy: This formula should do it:
    =SUMPRODUCT(Usage_Amount*(WEEKDAY(Call_Date,2)<6)*((HOUR(Call_Time+Call_Duration)<7)+(HOUR(Call_Time)>19)))
    ...where Usage_Amount, Call_Date, and Call_Duration are named ranges pointing to those columns.

    Or if you turn your data into an excel table (by pushing CTRL+T) then it would be this:
    =SUMPRODUCT(Table1[Usage amount]*(WEEKDAY(Table1[Call date],2)<6)*((HOUR(Table1[Call time]+Table1[Call duration])<7)+(HOUR(Table1[Call time])>19)))

    See the attached spreadsheet.Tuytuy.xlsb

  3. #3
    Seeker tuytuy's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    5
    Articles
    0
    i wanted to use a vba code / macro.
    i know how to do with a formula

  4. #4
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Ah. Why do you want to use VBA?

  5. #5
    Seeker tuytuy's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    5
    Articles
    0
    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.

  6. #6
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Okay. THe most efficient way is to leverage off of Excel's worksheet functions using VBA. (Note that you can't use the Application.WorksheetFunction method to call these functions from VBA because VBA doesn't handle SUMPRODUCT very well).

    So here's some approaches:

    If you set up dynamic named ranges, then you could use this:
    Evaluate("=SUMPRODUCT(Usage_Amount*(WEEKDAY(Call_Date,2)<6)*((HOUR(Call_Time+Call_Duration)<7)+(HOUR(Call_Time)>19)))")

    ...or if using Excel 2010 tables, you could use this:
    Evaluate("=SUMPRODUCT(Table1[Usage amount]*(WEEKDAY(Table1[Call date],2)<6)*((HOUR(Table1[Call time]+Table1[Call duration])<7)+(HOUR(Table1[Call time])>19)))")

    Or you could use VBA to write the formula to the spreadsheet itself, and then reference that cell, like this:

    Sub test()
    [B1].Formula = "=SUMPRODUCT(Usage_Amount*(WEEKDAY(Call_Date,2)<6)*((HOUR(Call_Time+Call_Duration)<7)+(HOUR(Call_Time)>19)))"
    Debug.Print [B1]
    End Sub

    Using VBA to read the entire range into an array and then iterate through it in order to calculate this could take a long time, and would be needlessly complicated.
    Last edited by JeffreyWeir; 2013-04-07 at 05:09 AM.

  7. #7
    Seeker tuytuy's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    5
    Articles
    0
    i'm really confused with your solution how do i include this in a longer code ? because i need the answer on a seperated sheet then the one where the table is. The sheet where the table is, is called Section_6 and where i want the answer is SynthesisVSD

  8. #8
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Will you be using the same workbook, and just adding new data in every week to your existing table? Or will you be using an entirely new workbook each time?

  9. #9
    Seeker tuytuy's Avatar
    Join Date
    Apr 2013
    Location
    A, A
    Posts
    5
    Articles
    0
    I will be using a whole new file every week that is why i would like to use a macro. So i just have to copy the code in and the report generate itself.

  10. #10
    Super Moderator JeffreyWeir's Avatar
    Join Date
    Mar 2011
    Location
    New Zealand
    Posts
    357
    Articles
    0
    Okay. Another quick question, and then I can work out the best way to do this. You previously said "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."

    WHat else will you be doing with code? Or is the purpose of the code purely to do this one thing?

Posting Permissions

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