+ Reply to Thread
Results 1 to 6 of 6

How do I? Hour by Hour Occurrences between a Date Range?

  1. #1
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    How do I? Hour by Hour Occurrences between a Date Range?

    Hi Guys,

    I have an Excel that I need help with. These dates represent the number of email submissions I got on my website. I'm trying to find out how to see what hours of the day are most popular.

    a) I need Excel to give me an hourly breakdown of submissions.
    b) I would also like to be able to enter a date range, so that I can enter say "Between November and December".
    c) If I can also enter a specific day to see breakdowns for that day only, such as Tuesday's, that would be great.

    Attached is an Excel. Any help is appreciated!

    For example:
    Date Start:
    Date End:

    Hourly breakdown:
    12am to 1am: Submissions
    1am to 2am: Submissions
    2am to 3am: Submissions
    etc.

    This is an example of how Column A looks in my Excel.
    1/28/13 11:08 AM
    1/26/13 6:08 PM
    1/25/13 11:51 AM
    1/24/13 8:36 PM
    1/24/13 2:47 PM
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,460

    Re: How do I? Hour by Hour Occurrences between a Date Range?

    See attached file for 3 summary tables.

    Hope this helps.

    Pete
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: How do I? Hour by Hour Occurrences between a Date Range?

    Pete, wonderful, thank you, that works! Is there a way to:

    1. Not set a limit on the rows, for example A$2:A$286 but instead A$2: "to Infinity" (so that anytime I add rows, it updates)

    2. For the "Selected Day" column, also have it by month? For example, "Wednesdays in January" or "Wednesdays in date range such as the last two weeks".

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,460

    Re: How do I? Hour by Hour Occurrences between a Date Range?

    The formulae use the SUMPRODUCT function, and with this it is better to make the range suit the data that you have rather than make it very large, as the performance will degrade with very large ranges. If you made it up to A$5000 this shouldn't be too detrimental, but it depends how much data you have in reality.

    The example tables I gave you covered the three types of query that you mentioned in your opening post. Of course, there are many other types of query that you could devise, like the 2 in your recent post - it's just a matter of deciding what you want to do and then coming up with the appropriate conditions in the SUMPRODUCT formula. By studying the formula I have given you, you should be able to work out how to set up the conditions that you have now mentioned.

    Hope this helps.

    Pete

  5. #5
    Forum Contributor
    Join Date
    07-17-2012
    Location
    N/A
    MS-Off Ver
    Excel 2010
    Posts
    218

    Re: How do I? Hour by Hour Occurrences between a Date Range?

    "By studying the formula I have given you, you should be able to work out how to set up the conditions that you have now mentioned."
    Thanks for the tip. After some studying of the cells, I found that it was as easy as adding this to the end
    *(INT(A$2:A$210)>=$I$2)*(INT(A$2:A$210)<=$I$3))

    For one of your commands, how do I remove the Hour requirement? I would just like it to count the occurrences for a given date range, without the hourly requirement.

    =SUMPRODUCT((MOD(A$2:A$210,1)>=E16)*(MOD(A$2:A$210,1)<E16+1/24)*(INT(A$2:A$210)>=$I$2)*(INT(A$2:A$210)<=$I$3))

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    20,460

    Re: How do I? Hour by Hour Occurrences between a Date Range?

    Glad you were able to solve it yourself - better than me just handing you the solution. A bit more background to help you with the next part:

    The way Excel stores dates internally is as a serial number measured from some reference date (1st Jan 1900). Hence they are just integers to Excel. Times are stored as fractions of a 24-hour day, so 12 noon is actually 0.5, 6:00pm is 0.75 etc. This means that times are always fractions, and a date/time value is made up of an integer part which represents the date and a fractional part which represents the time.

    The function INT returns the integer part of a real number (i.e. the date, which you can see in the formula is then compared with I2 and I3 in turn, which is where you have put the start and end dates).

    The function MOD returns the remainder after division, and in the formula I have used a divisor of 1, so MOD(number,1) returns the fractional part of the number, which I've said above equates to the time part of a date/time value. This is compared with E16 which contains a time and with E16+1/24, i.e. one hour later.

    So, can you see now which part(s) you need to remove?

    Hope this helps.

    Pete

+ 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