Search code examples
filterexcel-formulagoogle-sheets-formula

How to Create a List of Available Times after removing Downtimes from a Period

I have a grid which lists the Period (Start - End), and a list of Downtimes.

The downtimes are then sorted (to ensure chronological order based on the start time of the outage), using the following formula:

=SORT(INDIRECT("B4:C"&SUMPRODUCT(MAX((B4:B12<>"")*ROW(B4:B12)))),1)

After which I am trying to calculate the list of Available times (Uptimes).

Currently i have a mess of inflexible formulas as follows:

B26 =IF(B14<B15,B14,C15)

C26 =IF(C14<C15,C14,B16)

B27 =C16

C27 =B17

I am searching for either a universal single celled arrayformula, or a formula that can be dragged (down/across), that can calculate the list of Available times (Uptimes).

I am seeking formula solutions that will work in both Excel (Mac 2021) and Google Sheets.

See attached image: enter image description here

EDIT:

Here is a google sheet that has some example data, and explanatory notes: https://docs.google.com/spreadsheets/d/1t0XImtjP4RKeTdg3L97bjPzateUX2waHPhjf3nmSFIk/edit#gid=2100307022

Solution

  • Here is the solution i created (working in both Excel + Google Sheets) for cutting downtimes from a time period, to leave only the remaining uptimes.

    Using the same cells and ranges as per the Question....

    The original downtimes does not need to be in order, they are sorted in the intermediate table.

    in cell B15:

    =IFERROR(SORT(FILTER(B4:C12,(B4:B12<=C3)*(C4:C12>=B3))),"")

    Then, create named ranges for the elements to be used in the formulas that will populate the remaining uptimes (this makes the formulas easier to edit as will be noted below):

    start is the start time of the original time period (B14)

    end is the end time of the original time period (C14)

    downstart is the range of the start datetimes of the downtimes (B15:B23)

    downend is the range of the end datetimes of the downtimes (C15:C23)

    Then, to create/populate the list of the remaining uptimes, for the opening times, enter this formula into the first cell (B25) :

    =IFERROR(IF((start>=MIN(downstart))*(end<=MAX(downend)),IF(ROW()=(25+SUMPRODUCT(--(LEN(downstart)>0))-1),"",SMALL(downend,1+ROW()-25)),
IF((start>=MIN(downstart)),SMALL(downend,1+ROW()-25),
IF((end<=MAX(downend)),IF(ROW()=25,start,IF(ROW()=(25+SUMPRODUCT(--(LEN(downstart)>0))),"",SMALL(downend,ROW()-25))),
IF(ROW()=25,start,SMALL(downend,ROW()-25))
))),"")

    And for the ending times, enter this formula into the first cell (C25):

    =IFERROR(IF((start>=MIN(downstart))*(end<=MAX(downend)),IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),"",SMALL(downstart,2+ROW()-25)),
IF((start>=MIN(downstart)),IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),end,SMALL(downstart,2+ROW()-25)),
IF((end<=MAX(downend)),IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),end,SMALL(downstart,1+ROW()-25)),
IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))),end,SMALL(downstart,1+ROW()-25))
))),"")

    Note: In both of these formulas, the number 25 is the row number of the first row where the results are to be populated, so if you have these results starting on a different row, just change the number 25 to your starting row. Due to use of named ranges, no other changes are necessary.

    After entering the formulas, drag them both down to fill the remaining results.

    For those with Excel 2019 or newer (or Google Sheets), you can use IFS instead. For the opening times, use this (in B25 ):

    =IFERROR(IFS(
(start>=MIN(downstart))*(end<=MAX(downend)),(IF(ROW()=(25+SUMPRODUCT(--(LEN(downstart)>0))-1),"",SMALL(downend,1+ROW()-25))),
(start>=MIN(downstart)),SMALL(downend,1+ROW()-25),
(end<=MAX(downend)),(IF(ROW()=25,start,IF(ROW()=(25+SUMPRODUCT(--(LEN(downstart)>0))),"",SMALL(downend,ROW()-25)))),
(start<MIN(downstart))*(end>MAX(downend)),(IF(ROW()=25,start,SMALL(downend,ROW()-25)))
),"")

    And, for the ending times use this (in C25):

    =IFERROR(IFS(
(start>=MIN(downstart))*(end<=MAX(downend)),(IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),"",SMALL(downstart,2+ROW()-25))),
(start>=MIN(downstart)),(IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),end,SMALL(downstart,2+ROW()-25))),
(end<=MAX(downend)),(IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),end,SMALL(downstart,1+ROW()-25))),
(start<MIN(downstart))*(end>MAX(downend)),(IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))),end,SMALL(downstart,1+ROW()-25)))
),"")

    Again, drag them down to populate the remaining results.

    Explanation:

    (IF(ROW()=(25+SUMPRODUCT(--(LEN(downend)>0))-1),xxxxxx styled sections of the formulas check if on certain row, so that can return specific results

    SMALL(named_range,ROW()-(25)) styled sections of the formulas uses the ROW with an offset (25, based on this examples starting row for the results) to increment the SMALL

    Both the nested IF and the IFS styled solutions are in the example file linked in the opening Question.