Home

IF Function help!

%3CLINGO-SUB%20id%3D%22lingo-sub-555079%22%20slang%3D%22en-US%22%3EIF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-555079%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20find%20a%20function%20that%20will%20work%20for%20the%20following%3A%3C%2FP%3E%3CP%3EIf%20the%20hour%20of%20the%20day%20is%20from%208am%20to%205pm%2C%20then%20false%3C%2FP%3E%3CP%3EIf%20the%20hour%20of%20the%20day%20is%205pm%20to%208am%2C%20then%20true.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20data%20is%20formatted%20in%20hours%20with%20military%20time%20only%20in%20the%20field.%3C%2FP%3E%3CP%3EExample%3A%26nbsp%3B%2023%3A06%3C%2FP%3E%3CP%3EI%20want%20another%20column%20with%20the%20function%20to%20say%20that%2023%3A06%20is%20true%20because%20it's%20after%20business%20hours%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-555079%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-555701%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-555701%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339424%22%20target%3D%22_blank%22%3E%40cristacan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20believe%3C%2FSPAN%3E%20the%20clearest%20approach%20is%20to%20use%20a%20couple%20of%20spare%20cells%20to%20hold%20the%20beginning%20and%20end%20of%20the%20working%20day%20and%20name%20them%20'%3CSTRONG%3Estart%3C%2FSTRONG%3E'%20and%20'%3CSTRONG%3Eend%3C%2FSTRONG%3E'.%26nbsp%3B%20The%20values%20they%20actually%20hold%20are%201%2F3%20and%2035%2F48%20respectively%20but%20the%20significance%20of%20such%20numbers%20is%20not%20that%20obvious.%26nbsp%3B%20If%20you%20are%20interested%20in%20a%20single%20time%20or%20access%20times%20one%20by%20one%20using%20a%20relative%20reference%20then%20you%20could%20use%20the%20formula%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20MEDIAN(%20Time%2C%20Start%2C%20End%20)%26lt%3B%26gt%3BTime%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EIf%2C%20on%20the%20other%20hand%20you%20want%20to%20process%20an%20array%20of%20times%2C%20one%20could%20use%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20NOT(%20(timeArray%26gt%3B%3DStart)%20*%20(timeArray%26lt%3B%3DEnd)%20)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eor%20even%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3D%20(timeArray%3CSTART%3EEnd)%3C%2FSTART%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EOf%20course%2C%20you%20could%20always%20replace%20the%20Names%20by%20hard-wired%20values%20and%20direct%20cell%20references%20if%20you%20think%20that%20improves%20things.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-555731%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-555731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F339424%22%20target%3D%22_blank%22%3E%40cristacan%3C%2FA%3E%26nbsp%3BThis%20formula%20will%20show%20TRUE%20if%20the%20time%20value%20is%20between%208%20am%20and%205%20pm%20and%20FALSE%20if%20it%20is%20not.%20You%20don't%20need%20an%20IF%20statement%20to%20return%20TRUE%20or%20FALSE.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DAND(MOD(A1%2C1)%26gt%3B%3DTIME(8%2C0%2C0)%2CMOD(A1%2C1)%26lt%3B%3DTIME(17%2C0%2C0))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20format%20of%20the%20cell%20with%20the%20time%20value%20is%20not%20relevant.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-556416%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-556416%22%20slang%3D%22en-US%22%3EThis%20formula%20returns%20TRUE%2C%20if%20the%20time%20in%20A1%20is%20before%208%3A00%20AM%20or%20after%205%3A00%20PM%3B%20otherwise%2C%20it%20returns%20FALSE%3A%3CBR%20%2F%3E%3DOR(A1%3CTIME%3E%3C%2FTIME%3EA1%26gt%3BTIME(17%2C0%2C0))%3C%2FLINGO-BODY%3E
cristacan
Occasional Visitor

Trying to find a function that will work for the following:

If the hour of the day is from 8am to 5pm, then false

If the hour of the day is 5pm to 8am, then true. 

My data is formatted in hours with military time only in the field.

Example:  23:06

I want another column with the function to say that 23:06 is true because it's after business hours

3 Replies
Highlighted

@cristacan 

I believe the clearest approach is to use a couple of spare cells to hold the beginning and end of the working day and name them 'start' and 'end'.  The values they actually hold are 1/3 and 35/48 respectively but the significance of such numbers is not that obvious.  If you are interested in a single time or access times one by one using a relative reference then you could use the formula

= MEDIAN( Time, Start, End )<>Time

If, on the other hand you want to process an array of times, one could use

= NOT( (timeArray>=Start) * (timeArray<=End) )

or even

= (timeArray<Start) + (timeArray>End)

Of course, you could always replace the Names by hard-wired values and direct cell references if you think that improves things.

 

@cristacan This formula will show TRUE if the time value is between 8 am and 5 pm and FALSE if it is not. You don't need an IF statement to return TRUE or FALSE.

 

=AND(MOD(A1,1)>=TIME(8,0,0),MOD(A1,1)<=TIME(17,0,0))

 

The format of the cell with the time value is not relevant.

This formula returns TRUE, if the time in A1 is before 8:00 AM or after 5:00 PM; otherwise, it returns FALSE:
=OR(A1<TIME(8,0,0),
A1>TIME(17,0,0))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies