Forum Discussion
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...
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))
You could try a conditional format, custom function like this:
=AND(HOUR(A2)>6,HOUR(A2)<8)
It would highlight any row with a time in column A between 6 and 8
=AND(HOUR(A2)>6,HOUR(A2)<8)
It would highlight any row with a time in column A between 6 and 8
Many thanks JKPieterse,for the feedback, unfortunately that will not help because if the datetime range were different, it is possible that the times could be before 06:00 or after 08:00.
Here is another example to show you what I mean:
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))WowSergeiBaklan - That is amazing. :-)
Thanks you so much for your help. I am now going to investigate what you have done here as I am not yet familiar with the 'aggregate' formula.
