+ Reply to Thread
Results 1 to 8 of 8

Getting Sum of Data from Split Time Interval

  1. #1
    Registered User
    Join Date
    03-26-2015
    Location
    UAE
    MS-Off Ver
    Office 365
    Posts
    37

    Getting Sum of Data from Split Time Interval

    Hi,

    I have attached the sheet I am working on. I am looking for value lookup and sum functions in a way that only looks particular values within an hour and gives the total of that at the start of the hour. For Eg: the time could be any between 6 Am to 7Am I'm looking to add any visits Between that to just show up as 6Am. And so on. I have described in the excel sheet how that would look. Any help would be appreciated. Thanks!
    Attached Files Attached Files
    Last edited by mohdabrar; 01-31-2020 at 10:47 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Getting Sum of Data from Split Time Interval

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!

  3. #3
    Registered User
    Join Date
    03-26-2015
    Location
    UAE
    MS-Off Ver
    Office 365
    Posts
    37
    I'm so sorry. I'll update the thread.
    Last edited by AliGW; 02-01-2020 at 01:56 AM. Reason: Please don't quote unnecessarily!

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Getting Sum of Data from Split Time Interval

    This proposal employs three helper columns (C, H and O) which may be hidden (by hiding the column or changing the font to white) for aesthetic purposes.
    The helper columns are populated using formulas similar to: =TIMEVALUE(RIGHT(B12,5))
    J16:J86 are populated using: =SUMIFS(E$12:E$30,C$12:C$30,">"&H15,C$12:C$30,"<="&H16)
    Note that the formulas in J15 and J87 differ slightly.
    Q13:Q30 are populated using: =SUMIFS(J$15:J$87,H$15:H$87,">="&O13,H$15:H$87,"<"&$O14)
    Note that the formula in Q31 differs slightly.
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    03-26-2015
    Location
    UAE
    MS-Off Ver
    Office 365
    Posts
    37

    Re: Getting Sum of Data from Split Time Interval

    Thank you so much for this! It works, however, I've run into another problem. The data that I actually put in to the sheet to be calculated, the references keep changing, sometimes couple of time frames are missing or more time frames come into picture. Any way to get around that? So far, we've been doing the calculations and updating these numbers manually, which take a lot of time. I am just trying to change that. Any help would be appreciated. I've attached the sheet again for reference as to see what I mean by data cells getting changed. Any help on this would really save time on the reports. Also, I can't seem to figure out why the 23:00 mark doesn't get added in the final table. Thanks again! Refer to sheet 3 in the attachment.
    Attached Files Attached Files
    Last edited by mohdabrar; 02-04-2020 at 03:39 PM.

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Getting Sum of Data from Split Time Interval

    There is only one sheet in the attachment, TOD-D Sample (I checked for hidden sheets and found none).

  7. #7
    Registered User
    Join Date
    03-26-2015
    Location
    UAE
    MS-Off Ver
    Office 365
    Posts
    37
    Quote Originally Posted by JeteMc View Post
    There is only one sheet in the attachment, TOD-D Sample (I checked for hidden sheets and found none).
    Thanks for checking. I think I created duplicates and attached the wrong one. I was able to finally work around the problem I was facing. So I'll mark this thread as solved. I'll just leave the solution here so if others ever need to revisit. My data had visits and hours separately. All I did was assigned certain cells for visits and gave that reference for visits and same for hours and as far as the calculations not happening after 2300 I've to put that in as 2400 and not 0000. That fixed it. Thanks for getting back!

  8. #8
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Getting Sum of Data from Split Time Interval

    Glad that you found a solution. To mark the thread as 'Solved' use the thread tools menu above your first post. I hope that you have a blessed day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 1
    Last Post: 04-29-2014, 04:42 AM
  2. [SOLVED] SUM data that falls within a time interval
    By shelsx in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-12-2012, 11:17 PM
  3. Pasting data at regular time interval
    By Navin Agrawal in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-05-2012, 02:50 AM
  4. Replies: 0
    Last Post: 04-23-2012, 10:06 AM
  5. how to re-divide the data at the same time interval
    By wetlander in forum Excel General
    Replies: 9
    Last Post: 04-19-2009, 05:32 AM
  6. adding data for time interval
    By sabsay in forum Excel General
    Replies: 0
    Last Post: 03-17-2008, 02:40 AM

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