+ Reply to Thread
Results 1 to 12 of 12

Help in categorising data into 30mins intervals and summing the value

  1. #1
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Help in categorising data into 30mins intervals and summing the value

    Hello,
    Would really appreciate anyone that could help me figure this one out.
    Last edited by auswtz; 03-30-2013 at 09:00 PM.

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help in categorising data into 30mins intervals and summing the value

    do you intend say 00:30 being counted twice?
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Help in categorising data into 30mins intervals and summing the value

    Hi martindwilson,
    No, I was thinking of changing the format to below if it helps?
    00:00-00:29
    00:30-00:59
    01:00-01:29

  4. #4
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help in categorising data into 30mins intervals and summing the value

    maybe this
    Attached Files Attached Files
    Last edited by martindwilson; 03-24-2013 at 09:52 PM.

  5. #5
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Help in categorising data into 30mins intervals and summing the value

    Thank you so much martindwilson
    Last edited by auswtz; 03-30-2013 at 09:02 PM.

  6. #6
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Help in categorising data into 30mins intervals and summing the value

    see attached file - sheet 2
    Last edited by auswtz; 03-30-2013 at 09:01 PM.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help in categorising data into 30mins intervals and summing the value

    nothing on sheet 2

  8. #8
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Help in categorising data into 30mins intervals and summing the value

    Sorry here it is.
    Last edited by auswtz; 03-30-2013 at 09:04 PM.

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help in categorising data into 30mins intervals and summing the value

    i cant see the difference, i split column j with text to columns using - as delimiter then used find replace to replace all the - in cols b+c with 0

  10. #10
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Help in categorising data into 30mins intervals and summing the value

    difference would be in Column A...Instead of starting time of 00:00-00:30, the raw data may commence on 04:00-0:4:15
    Column A may not have the complete 24hours (15minutes intervals) as it will skip on times that has no calls entering.

    Thanks again for your help with this, Martin
    Last edited by auswtz; 03-30-2013 at 09:03 PM.

  11. #11
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Help in categorising data into 30mins intervals and summing the value

    cant see the difference it will give 0 for any gaps and say 7:15 is in there but not 7:00 it still counts that in the 7:00-7:30 slot (oh and column a are text you need to change those to real times ,put 0 in a spare cell,copy it,select times in col a paste special add,then reformat col a to [h]:mm
    Last edited by martindwilson; 03-24-2013 at 11:09 PM.

  12. #12
    Registered User
    Join Date
    03-24-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Help in categorising data into 30mins intervals and summing the value

    Oh don't mind me..you're completely right thank you so much for your help martin. Love your work!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1