+ Reply to Thread
Results 1 to 9 of 9

Count of date ranges over any given time period.

  1. 12-15-2014, 02:28 PM #1
    happyj
    happyj is offline
    Registered User
    Join Date
    12-15-2014
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    4

    Count of date ranges over any given time period.

    Searched quite a bit, think my post is unique.

    I have a script that logs each date/time a user has a license activated and when it is deactivated, along with that duration. The output is:

    Name Start Finish Duration
    Jon 12/16/14 9:00 12/16/14 9:30 00:30
    Mark 12/16/14 9:20 12/16/14 9:30 00:10
    Tom 12/16/14 9:25 12/16/14 9:45 00:20

    I want to determine how many users are using the license at any given time. I've used countifs and sumproduct without success. A graphical output is most appropriate to show usage over a specific date range. I thought that querying a date/time range every minute and counting the number of users on for each minute then output to a graph made sense...just can't figure it out. Thanks for any guidance!
    Reply With QuoteRegister To Reply
  2. 12-15-2014, 02:46 PM #2
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Count of date ranges over any given time period.

    if dates are numbers then:
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  3. 12-15-2014, 03:06 PM #3
    happyj
    happyj is offline
    Registered User
    Join Date
    12-15-2014
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    4

    Re: Count of date ranges over any given time period.

    Thanks but I'm not quite sure what your formula is doing. I originally made a separate column that included my date/time range of interest with each cell representing 1 minute. I attempted to 'count' how many 'licenses/users' occurred during that minute. So with the above date if the added cell had a value of '12/16/14 9:29', it returned 3. But I had issues getting that to work properly also.
    Reply With QuoteRegister To Reply
  4. 12-15-2014, 03:16 PM #4
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Count of date ranges over any given time period.

    add more data, change accordingly formula and try it with timings you are sure of
    Reply With QuoteRegister To Reply
  5. 12-15-2014, 03:23 PM #5
    happyj
    happyj is offline
    Registered User
    Join Date
    12-15-2014
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    4

    Re: Count of date ranges over any given time period.

    Right, so what I believe your formula is doing is comparing the date ranges against itself. What I need it to do is count that range at a specific moment in time. Your formula says that the maximum count within that date/time range is 3; that is correct for that entire range of dates/times. But what I want is, what is the max count at say 12/16/14 9:29, or 12/16/14 9:45, which would be 3 and 1 respectively.
    Reply With QuoteRegister To Reply
  6. 12-15-2014, 03:34 PM #6
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Count of date ranges over any given time period.

    Tim's formula is well constructed; it tells you the maximum licenses active over a period.

    Suppose you had these begin and end times:

    Row\Col
    B
    C
    1
    Start
    Finish
    2
    12/16/2014 08:00
    12/16/2014 08:57
    3
    12/16/2014 08:01
    12/16/2014 08:22
    4
    12/16/2014 08:21
    12/16/2014 08:49
    5
    12/16/2014 08:30
    12/16/2014 08:40
    6
    12/16/2014 08:30
    12/16/2014 08:40
    7
    12/16/2014 08:50
    12/16/2014 09:12
    8
    12/16/2014 09:01
    12/16/2014 09:41
    9
    12/16/2014 09:14
    12/16/2014 09:19
    10
    12/16/2014 09:30
    12/16/2014 10:17
    11
    12/16/2014 09:40
    12/16/2014 10:37
    12
    12/16/2014 09:58
    12/16/2014 10:08
    13
    12/16/2014 10:11
    12/16/2014 11:07
    14
    12/16/2014 10:15
    12/16/2014 10:50
    15
    12/16/2014 10:20
    12/16/2014 10:53
    16
    12/16/2014 10:24
    12/16/2014 11:04
    17
    12/16/2014 10:46
    12/16/2014 11:40
    18
    12/16/2014 10:55
    12/16/2014 11:09


    Then you could list the times by minute:

    Row\Col
    F
    G
    H
    I
    1
    Time
    Users
    Change
    2
    12/16/2014 08:00
    1
    1
    		 G2: =COUNTIFS($B$2:$B$20, "<=" & F2, $C$2:$C$20, ">" & F2)
    3
    12/16/2014 08:01
    2
    1
    		 H2: =--(G2 <> G1)
    4
    12/16/2014 08:02
    2
    0
    		 F3: =MROUND(F2+1/1440, 1/1440)
    5
    12/16/2014 08:03
    2
    0
    6
    12/16/2014 08:04
    2
    0
    7
    12/16/2014 08:05
    2
    0
    8
    12/16/2014 08:06
    2
    0
    9
    12/16/2014 08:07
    2
    0
    10
    12/16/2014 08:08
    2
    0
    11
    12/16/2014 08:09
    2
    0
    12
    12/16/2014 08:10
    2
    0
    13
    12/16/2014 08:11
    2
    0
    14
    12/16/2014 08:12
    2
    0
    15
    12/16/2014 08:13
    2
    0
    16
    12/16/2014 08:14
    2
    0
    17
    12/16/2014 08:15
    2
    0
    18
    12/16/2014 08:16
    2
    0
    19
    12/16/2014 08:17
    2
    0
    20
    12/16/2014 08:18
    2
    0
    21
    12/16/2014 08:19
    2
    0
    22
    12/16/2014 08:20
    2
    0
    23
    12/16/2014 08:21
    3
    1
    24
    12/16/2014 08:22
    2
    1
    25
    12/16/2014 08:23
    2
    0
    26
    12/16/2014 08:24
    2
    0
    27
    12/16/2014 08:25
    2
    0
    28
    12/16/2014 08:26
    2
    0
    29
    12/16/2014 08:27
    2
    0
    30
    12/16/2014 08:28
    2
    0
    31
    12/16/2014 08:29
    2
    0
    32
    12/16/2014 08:30
    4
    1
    33
    12/16/2014 08:31
    4
    0


    Then filter the Change column for 1's:

    Row\Col
    F
    G
    H
    1
    Time
    Users
    Change
    2
    12/16/2014 08:00
    1
    1
    3
    12/16/2014 08:01
    2
    1
    23
    12/16/2014 08:21
    3
    1
    24
    12/16/2014 08:22
    2
    1
    32
    12/16/2014 08:30
    4
    1
    42
    12/16/2014 08:40
    2
    1
    51
    12/16/2014 08:49
    1
    1
    52
    12/16/2014 08:50
    2
    1
    59
    12/16/2014 08:57
    1
    1
    63
    12/16/2014 09:01
    2
    1
    74
    12/16/2014 09:12
    1
    1
    76
    12/16/2014 09:14
    2
    1
    81
    12/16/2014 09:19
    1
    1
    92
    12/16/2014 09:30
    2
    1
    102
    12/16/2014 09:40
    3
    1
    103
    12/16/2014 09:41
    2
    1
    120
    12/16/2014 09:58
    3
    1
    130
    12/16/2014 10:08
    2
    1
    133
    12/16/2014 10:11
    3
    1
    137
    12/16/2014 10:15
    4
    1
    139
    12/16/2014 10:17
    3
    1
    142
    12/16/2014 10:20
    4
    1
    146
    12/16/2014 10:24
    5
    1
    159
    12/16/2014 10:37
    4
    1
    168
    12/16/2014 10:46
    5
    1
    172
    12/16/2014 10:50
    4
    1
    175
    12/16/2014 10:53
    3
    1
    177
    12/16/2014 10:55
    4
    1
    186
    12/16/2014 11:04
    3
    1
    189
    12/16/2014 11:07
    2
    1
    191
    12/16/2014 11:09
    1
    1
    222
    12/16/2014 11:40
    0
    1
    Entia non sunt multiplicanda sine necessitate
    Reply With QuoteRegister To Reply
  7. 12-15-2014, 03:42 PM #7
    tim201110
    tim201110 is offline
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Count of date ranges over any given time period.

    sorry, i're right
    in this case even shorter
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 12-15-2014, 03:43 PM #8
    happyj
    happyj is offline
    Registered User
    Join Date
    12-15-2014
    Location
    NJ
    MS-Off Ver
    2010
    Posts
    4

    Re: Count of date ranges over any given time period.

    Yes! That's it, surprisingly my countifs formula, I thought, was identical. However, the real beauty is the change column and filtering!

    Thank you both very much for the help
    Reply With QuoteRegister To Reply
  9. 12-15-2014, 03:50 PM #9
    shg
    shg is offline
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Count of date ranges over any given time period.

    You're welcome.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Identify if a date/time period exists within a calender period date and time
    By bajdr47 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-25-2013, 10:03 AM
  2. [SOLVED] Time as an elapsed value, not date and time or period between two date times
    By stephenbooth_uk in forum Excel General
    Replies: 2
    Last Post: 05-31-2013, 12:50 PM
  3. Formula for Date/Time count across multiple ranges
    By nigo75 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-29-2013, 09:24 PM
  4. count between two time & date ranges
    By Davis.Gray in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-07-2013, 01:51 PM
  5. Calculate Accounting period based on 12 date ranges
    By nikk05 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-30-2012, 08:07 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1