Creating time of day frequency chart

Question

I want to create a chart that shows how many orders are active at a certain time of day in excel, below is an example data set.

At any given time for one day I would like to visually see how many active orders so lets say at 9:45am there are 3 orders that are still active and at 12:00 there are no active orders. It would work well if one axis is time of day and the other axis is amount of active orders but mostly i do not know how to calculate the amount of active orders. Is there a way to graph this data? I think a line chart would be good to represent this data. I am mostly looking at turn around time and also highlighting the length of times when active orders is 0.

I mostly need to know how to count active orders at a given time

Are you using VBA for this? Nothing in your question suggests that you're trying to code something. There are more efficient ways to do this no doubt, but you could set up a loop from the first time of order received to the last time order submitted (8:17 to 14:07 in this case) and for each minute in this time, check how many orders are active. Then, keep track of this total for each loop, and plot the result. — TehDrunkSailor, Aug 01 '23 at 16:44
This could be done with a COUNTIFS. What is your expected output? — cybernetic.nomad, Aug 01 '23 at 16:56
I don't have to use vba but I'm open to it if it is the easiest option. My issue is I don't know how to check how many are active at a certain time... would I have to use count if for each minute of day? — user21920024, Aug 01 '23 at 17:05
So use a constraint in the countifs() ie "<" less than a value, ">" greater than a value and ">=" is greater than or equal to. — Solar Mike, Aug 01 '23 at 17:20
What do you understand by "active orders"? The ones received and not submitted? Or what? If so, your picture example is not so relevant, I would say. If you would make the chart, calculation at 14:07, all of them are closed. Doing that when the table looks as you show does it have any relevance? When/in which circumstances do you want the required solution to be working? — FaneDuru, Aug 02 '23 at 10:49
I would like a line graph showing each hour lets say. So at 1pm, 2pm, 3pm etc I consider an active order one that is received but not submitted! — user21920024, Aug 02 '23 at 19:49

score 0 · Answer 1 · answered Aug 02 '23 at 11:40

Please, try the next function:

    Dim arr, arrFin, i As Long, count As Long
    arr = rng.Value2
    ReDim arrFin(UBound(arr) - 1)
    For i = 1 To UBound(arr)
        If arr(i, 2) <= CDbl(CDate(dTime)) Then
            If arr(i, 3) >= CDbl(CDate(dTime)) Then
                arrFin(count) = arr(i, 1): count = count + 1
            End If
        Else
            Exit For
        End If
    Next i
    If count > 0 Then
        ReDim Preserve arrFin(count - 1)
        Debug.Print Join(arrFin, "|") 'just to see the open orders, if any...
        openOrders = count
    End If
 End Function

It can be used in the next testing sub:

Sub testOpenOrders()
   Debug.Print openOrders(Range("B2:D9"), "09:45")
   Debug.Print openOrders(Range("B2:D9"), "12:00")
End Sub

Or use it as UDF (User Defined Function) in a formula:

  =openOrders(B2:D9,"9:45")

It will return the number of open orders. In Immediate Window you can (also) see which orders are open...

@user21920024 Didn't you find some time to test the above code? If tested, didn't it do what you need? — FaneDuru, Aug 04 '23 at 17:44

Creating time of day frequency chart

1 Answers1