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 illustrate what I mean. The cells in orange are the ones with the time closest to 07:00.
Any help would be much appreciated.
Thanks,
Martin
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))
7 Replies
- 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 8Many 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))
