SOLVED
Home

Formula to identify the time closest to 07:00

%3CLINGO-SUB%20id%3D%22lingo-sub-459116%22%20slang%3D%22en-US%22%3EFormula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459116%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20be%20able%20to%20identify%20which%20cells%20have%20the%20time%20which%20is%20closest%20to%2007%3A00%20on%20any%20given%20day.%26nbsp%3B%20Does%20anyone%20know%20how%20this%20might%20be%20done%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20image%20to%20illustrate%20what%20I%20mean.%26nbsp%3B%20The%20cells%20in%20orange%20are%20the%20ones%20with%20the%20time%20closest%20to%2007%3A00.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20187px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109046iA312AB9DF37F32C4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%2004-16-19%20at%2009.46%20AM.PNG%22%20title%3D%22Screen%20Shot%2004-16-19%20at%2009.46%20AM.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20much%20appreciated.%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EMartin%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-459116%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459339%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459339%22%20slang%3D%22en-US%22%3EYou%20could%20try%20a%20conditional%20format%2C%20custom%20function%20like%20this%3A%3CBR%20%2F%3E%3DAND(HOUR(A2)%26gt%3B6%2CHOUR(A2)%26lt%3B8)%3CBR%20%2F%3EIt%20would%20highlight%20any%20row%20with%20a%20time%20in%20column%20A%20between%206%20and%208%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459382%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459382%22%20slang%3D%22en-US%22%3E%3CP%3EMany%20thanks%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%2Cfor%20the%20feedback%2C%20unfortunately%20that%20will%20not%20help%20because%20if%20the%20datetime%20range%20were%20different%2C%20it%20is%20possible%20that%20the%20times%20could%20be%20before%2006%3A00%20or%20after%2008%3A00.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20another%20example%20to%20show%20you%20what%20I%20mean%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20117px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109057i1BDB890DE69AE2B9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Screen%20Shot%2004-16-19%20at%2010.34%20AM.PNG%22%20title%3D%22Screen%20Shot%2004-16-19%20at%2010.34%20AM.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459636%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459636%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321790%22%20target%3D%22_blank%22%3E%40martin77%3C%2FA%3E%26nbsp%3B%2C%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20235px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F109063i4045A3E61D39BCC1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eit%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DA2%3DIF(INT(A2)%2B7%2F24-AGGREGATE(14%2C6%2C1%2F(INT(A2)%2B7%2F24%26gt%3B%24A%242%3A%24A%2426)*%24A%242%3A%24A%2426%2C1)%26lt%3B%0A%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F(INT(A2)%2B7%2F24%26lt%3B%3D%24A%242%3A%24A%2426)*%24A%242%3A%24A%2426%2C1)-(INT(A2)%2B7%2F24)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(14%2C6%2C1%2F(INT(A2)%2B7%2F24%26gt%3B%24A%242%3A%24A%2426)*%24A%242%3A%24A%2426%2C1)%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AGGREGATE(15%2C6%2C1%2F(INT(A2)%2B7%2F24%26lt%3B%3D%24A%242%3A%24A%2426)*%24A%242%3A%24A%2426%2C1))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459756%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459756%22%20slang%3D%22en-US%22%3E%3CP%3EWow%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%20-%20That%20is%20amazing.%26nbsp%3B%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20you%20so%20much%20for%20your%20help.%26nbsp%3B%20I%20am%20now%20going%20to%20investigate%20what%20you%20have%20done%20here%20as%20I%20am%20not%20yet%20familiar%20with%20the%20'aggregate'%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459811%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459811%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321790%22%20target%3D%22_blank%22%3E%40martin77%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Martin%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAGGREGATE%20finds%20largest%20(first%20parameter%2014)%20or%20smallest%20(15)%20value%20in%20the%20array%20(third%20parameter)%20ignoring%20all%20errors%20(second%20parameter%20%3D%206).%20Array%20is%20multiplication%20of%201%2F(...)%2C%20which%20gives%20ignored%20error%20if%20time%20is%20more%20than%2Fless%20than%207am%20for%20given%20date%2C%20on%20dates%20itself.%20Thus%20first%20part%20gives%20error%20or%201%2C%20multiplied%20on%20the%20date%20it%20returns%20error%20or%20date.%3C%2FP%3E%0A%3CP%3EFinding%20closest%20early%20time%20and%20closest%20later%20time%20we%20compare%20it%2C%20and%20depends%20on%20where%20the%20difference%20is%20less%20take%20one%20or%20another.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20I%20forgot%20that's%20to%20test%20if%20you%20have%20exactly%207%3A00%20in%20your%20date%2Ftime%2C%20perhaps%20we%20shall%20use%20%26lt%3B%3D%20(or%20%26gt%3B%3D)%20in%20one%20of%20conditions%20instead%20of%20%26lt%3B%20or%20%26gt%3B%20(AGGREGATE%20will%20return%20exact%20date%20in%20both%20cases)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-459906%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-459906%22%20slang%3D%22en-US%22%3E%3CP%3EI'll%20test%20if%20it%20falls%20at%2007%3A00%20and%20will%20study%20the%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%2C%20you%20are%20a%20legend%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-460827%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20identify%20the%20time%20closest%20to%2007%3A00%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-460827%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F321790%22%20target%3D%22_blank%22%3E%40martin77%3C%2FA%3E%26nbsp%3B%2C%20thank%20you%2C%20you%20are%20welcome%3C%2FP%3E%3C%2FLINGO-BODY%3E
martin77
New Contributor

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.

 

Screen Shot 04-16-19 at 09.46 AM.PNG

 

Any help would be much appreciated.

Thanks,

Martin

 

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 8

Many thanks @Jan Karel Pieterse,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:

 

Screen Shot 04-16-19 at 10.34 AM.PNG

 

 

Solution

@martin77 , for such sample

image.png

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))

Wow@Sergei Baklan - 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.

 

 

@martin77 ,

 

Hi Martin,

 

AGGREGATE finds largest (first parameter 14) or smallest (15) value in the array (third parameter) ignoring all errors (second parameter = 6). Array is multiplication of 1/(...), which gives ignored error if time is more than/less than 7am for given date, on dates itself. Thus first part gives error or 1, multiplied on the date it returns error or date.

Finding closest early time and closest later time we compare it, and depends on where the difference is less take one or another.

 

What I forgot that's to test if you have exactly 7:00 in your date/time, perhaps we shall use <= (or >=) in one of conditions instead of < or > (AGGREGATE will return exact date in both cases)

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

 

Thanks again, you are a legend @Sergei Baklan 

@martin77 , thank you, you are welcome

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies