Forum Discussion

martin77's avatar
martin77
Copper Contributor
Apr 16, 2019
Solved

Formula to identify the time closest to 07:00

Hi,   I would like to be able to identify which cells have the time which is closest to 07:00 on any given day.  Does anyone know how this might be done?   Please see the attached image to illust...
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 16, 2019

    martin77 , for such sample

    it could be

    =A2=IF(INT(A2)+7/24-AGGREGATE(14,6,1/(INT(A2)+7/24>$A$2:$A$26)*$A$2:$A$26,1)<
       AGGREGATE(15,6,1/(INT(A2)+7/24<=$A$2:$A$26)*$A$2:$A$26,1)-(INT(A2)+7/24),
                     AGGREGATE(14,6,1/(INT(A2)+7/24>$A$2:$A$26)*$A$2:$A$26,1),
                     AGGREGATE(15,6,1/(INT(A2)+7/24<=$A$2:$A$26)*$A$2:$A$26,1))
martin77's avatar
martin77
Copper Contributor
Apr 16, 2019

I'll test if it falls at 07:00 and will study the formula.

 

Thanks again, you are a legend SergeiBaklan 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 16, 2019

martin77 , thank you, you are welcome