Maximum Values within a Specific Hour Each Day

Harry has a list of sensor readings in a worksheet. Column A contains the date and time of the reading, and column B contains the actual reading. Since these are raw sensor readings, they are in ascending order based on column A. Harry would like to determine the maximum sensor reading that occurs between 8:00 pm and 9:00 pm each day.

The way you should approach this task depends, in large part, on the version of Excel you are using. If you want something that will work in any version of Excel, then start by putting together a list of dates that are represented in column A. You can put this list in column D and put it together manually, or you could assemble the date list automatically if you use the UNIQUE function. (This function is available in Excel 2021 and Microsoft 365.)

=UNIQUE(INT(A2:A10000))

With the date list constructed, we'll assume that the first date is in cell D2. In cell E2 you can place the following formula:

=MAX((IF((A:A>=(D2+TIME(20,0,0)))*(A:A<=(D2+TIME(21,0,0))),B:B)))

Copy the formula down as many cells as necessary, and you'll have the maximum reading between 8:00 pm and 9:00 pm for each date.

If you are using Excel 2019, Excel 2021, Excel 2024, or Microsoft 365, you could use the MAXIFS function. Place the following into cell E2 and copy down as many cells as necessary:

=MAXIFS(B:B,A:A,">="&D2+(20/24),A:A,"<="&D2+(21/24))

Note, as well, that this formula doesn't use the TIME function to calculate the beginning and ending times. Instead, it simply divides 20 (the hour number for 8:00 pm) and then does the same thing for the ending hour. You can use this approach or the TIME function approach; either will work fine.

If you are using Microsoft 365, you can use a formula that will spill both the dates and the maximum reading that occurred between 8:00 pm and 9:00 pm on those dates. Here is the formula broken out to multiple lines for easier understanding:

=LET(
    TD, TRIMRANGE(A2:A10000),
    RD, TRIMRANGE(B2:B10000),
    u, UNIQUE(INT(TD)),
    HSTACK(
        u,
        MAP(
            u,
            LAMBDA(
                x,
                MAXIFS(
                    RD,
                    TD, ">="&x+(20/24),
                    TD, "<"&x+(21/24)
                )
            )
        )
    )
)

TD references all the dates and RD all the readings for those dates. The u variable is used to extract a unique range of dates from the dates and times. The MAP function processes each of the unique dates individually, while the LAMBDA function uses MAXIFS to determine the maximum reading in the desired hour.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13967) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.