Calculate number of event days in a moving window

[demeeder]

I am trying build a tool that will calculate the when a total number of event days in a moving 365 day window exceeds a specific number (220).

Suppose I have 5 events. Each event has a start date and an end date. I can easily count the number of days for each event, but can't figure out how to build an alert when I will exceed 220 total days (not consecutive) in a 365 window (that is moving every day). I want to predict when/if I will every hit 220 well before the known event either starts or is completed.

In this example, you will see that I will exceed 220 on 12/29/2021. That's easy because my start date in #1 is 1/1/2021 and the end date is obviously less than 365 days out.

Book1
	A	B	C	D	E	F
1		1	2	3	4	5
2	Start	1/1/21	6/4/21	10/1/21	4/5/22	6/15/22
3	End	5/1/21	6/15/21	12/29/21	5/15/22	11/15/22
4		120	11	89	40	153
5		120	131	220	260	413
6	>220 days in a 365 window?
7	If so, what date crosses 220?
Sheet1

Cell Formulas
Range	Formula
B4:F4	B4	=B3-B2
B5	B5	=B4
C5:F5	C5	=B5+C4

Thanks!

 

[Anthony47]

It wouldn't be impossible to get the result by formula, but it's easier getting it via a User Function.

Fo example, copy this code into a standard module of your vba project:

Function myEventsInfo(ByRef SDates As Range, ByRef EDates As Range, Optional myThr As Long = 220) As Variant
'https://www.mrexcel.com/board/threads/calculate-number-of-event-days-in-a-moving-window.1206388/
'
'USAGE:
'   =myEventsInfo(StartDatesRange, EndDatesInterval [, Threshold])
'
'Example:   =myEventsInfo($B2:D2;$B3:D3)
'
'Will return 4 info:
'   The Used Days at beginning of the event
'   The Days the last event will last
'   The cumulative Used days at the end of the Event
'   The date that the Threshold will be reached
'
'   The default Threshold is 220, or it can be specified as the third parametre
'
Dim OArr(1 To 4, 1 To 1), I As Long, evDays As Long, J As Long
Dim BLE As Date, BCE As Date, UsedD As Long, dUsedD As Long
Dim dBLE As Date
'
If SDates.Rows.Count > 1 Then myEventsInfo = CVErr(2022): Exit Function
If EDates.Rows.Count > 1 Then myEventsInfo = CVErr(2022): Exit Function
If SDates.Columns.Count <> EDates.Columns.Count Then myEventsInfo = CVErr(2022): Exit Function
'
BLE = SDates.Cells(1, SDates.Columns.Count)
If EDates.Cells(1, SDates.Columns.Count) < BLE Or BLE = 0 Then
    myEventsInfo = Application.WorksheetFunction.Transpose(Array(0, 0, 0))
    Exit Function
End If
'Counting Used Day at beginning last event:
For I = 1 To SDates.Columns.Count - 1
    If SDates.Cells(1, I) <= (BLE - 365) Then BCE = BLE - 365 Else BCE = SDates.Cells(1, I)
    If BCE <= EDates.Cells(1, I) And EDates.Cells(1, I) < BLE Then UsedD = UsedD + EDates.Cells(1, I) - BCE + 1
Next I
OArr(1, 1) = UsedD
evDays = EDates.Cells(1, SDates.Columns.Count) - BLE + 1
OArr(2, 1) = evDays
'
OArr(3, 1) = OArr(1, 1) + evDays
'If Threshold is exceded then refine the calculation:
If (OArr(1, 1) + OArr(2, 1)) >= myThr Then
    For J = 0 To evDays '- 1
''    If J >= (evDays -2) Then Stop
        dUsedD = 0
        dBLE = BLE + J
        For I = 1 To SDates.Columns.Count - 1
            dBLE = BLE + J
            If SDates.Cells(1, I) <= (dBLE - 365) Then BCE = dBLE - 365 Else BCE = SDates.Cells(1, I)
            If BCE <= EDates.Cells(1, I) Then dUsedD = dUsedD + EDates.Cells(1, I) - BCE + 1
        Next I
'
        If (dUsedD + J) >= myThr And OArr(4, 1) = "" Then
            OArr(4, 1) = dBLE - 1
        End If
    Next J
    OArr(3, 1) = dUsedD + OArr(2, 1) - 0
End If
If OArr(4, 1) = "" Then OArr(4, 1) = ""
myEventsInfo = OArr
End Function

Now go to B5 of your worksheet and insert the formula

=myEventsInfo($B2:B2,$B3:B3)

If your version does support Dynamic Arrays (ex Office 365) you will enter the formula using Enter, and the formula will return 4 rows, filling B5:B8
If your version does NOT support Dynamic Arrays then you need to select B5:B8, copy the formula in the formula bar, then confirm it using Contr-Shift-Enter

The syntax of the function is:

=myEventsInfo(StartDatesRange, EndDatesInterval [, Threshold])

Threshold is optional, its default value is 220

The 4 rows returned by the function will state:
- The Used Days at beginning of the event
- The Days the last event will last
- The cumulative Used days at the end of the Event
- The date that the Threshold will be reached

Now you may copy B5 to the right to get the information about the other Events; if the formula has been enterd using Contr-Shifr-Enter thyen you have to copy the entire block (B5:B8) and paste to the next columns

HTH

 

[demeeder]

Thanks!! I am out for the weekend but will apply your solution on Monday and provide any feedback then. Much appreciated!!!

Dave

 

[Anthony47]

I forgot to attach a sample of results (see image)

 

[demeeder]

I believe this works well. Grazie!!!

Works for me on my home computer. Will see if the work version of Excel appreciates the VBA.