Using IF with hour formed cells.

%3CLINGO-SUB%20id%3D%22lingo-sub-1864267%22%20slang%3D%22en-US%22%3EUsing%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864267%22%20slang%3D%22en-US%22%3EI%20need%20to%20make%20a%20condition%3A%3CBR%20%2F%3EIf%20entrance%20hour%20is%20between%2022%3A00%20and%2005%3A00...%3CBR%20%2F%3EHow%20can%20I%20do%20it%20using%20if%20or%20ifs%20formula%3F%3CBR%20%2F%3EThanks.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1864267%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-1864284%22%20slang%3D%22de-DE%22%3ESubject%3A%20Using%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864284%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F861156%22%20target%3D%22_blank%22%3E%40itaytr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend.%20It%20can%20help%20us%20all%20if%20you%20upload%20an%20Excel%20file%20(without%20sensitive%20data)%2C%20no%20picture.%20Even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%20This%20would%20also%20be%20a%20blessing%20for%20all%20of%20us%2C%20as%20we%20can%20understand%20the%20problem%20much%20better%2C%20a%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864305%22%20slang%3D%22en-US%22%3EBetreff%3A%20Using%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864305%22%20slang%3D%22en-US%22%3EI%20attached%20a%20file%20for%20example.%3CBR%20%2F%3EWaiting%20for%20a%20solution.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864324%22%20slang%3D%22de-DE%22%3ESubject%3A%20Using%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864324%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F861156%22%20target%3D%22_blank%22%3E%40itaytr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWhen%20the%20admission%20hour%20is%20between%2010%3A00%20PM%20and%205%3A00%20AM%20...%3C%2FSPAN%3E%20%3CSPAN%3EWhat%20should%20happen%20here%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EExcel%20is%20from%20the%20Arabic%20or%20Hebrew%20region.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EFrom%20right%20to%20left.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20your%20Excel%20sheet%20you%20only%20have%20these%20lines%3C%2FSPAN%3E%3C%2FP%3E%3CTABLE%20width%3D%2295%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2295%22%3Eentering%20hour%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E22%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E19%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E23%3A15%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E06%3A00%3A00%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E04%3A15%3A00%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CSPAN%3E...%20What%20should%20become%20of%20them%3F%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E...%20and%20as%20already%20written%2C%20please%20state%20the%20Excel%20version%20and%20operating%20system%2C%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eif%20possible%20exactly%20(e.g.%20Excel%20365%20Pro%20or%20Win10-2004).%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EForgive%20us%20for%20the%20circumstances%2C%20but%20if%20no%20precise%20task%20can%20be%20set%20and%20no%20precise%20instructions%20can%20be%20given%2C%20then%20in%20the%20best%20case%2C%20only%20an%20imprecise%20solution%20can%20be%20suggested%20to%20you.%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%20for%20your%20patience%20and%20understanding%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864328%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864328%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F861156%22%20target%3D%22_blank%22%3E%40itaytr%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20the%20comparison%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DOR((B5%26gt%3B--%2222%3A00%3A00%22)%2C(B5%26lt%3B--%2205%3A00%3A00%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864331%22%20slang%3D%22en-US%22%3EBetreff%3A%20Using%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864331%22%20slang%3D%22en-US%22%3EHi%20Nikolino%2C%3CBR%20%2F%3EI%20updated%20the%20file%20and%20added%20a%20column%20with%20what%20I%20want%20to%20achieve.%3CBR%20%2F%3EI'm%20using%20excel%20in%20my%20android%20phone.%3CBR%20%2F%3EI'll%20try%20to%20be%20specific%20about%20what%20I%20want%3A%3CBR%20%2F%3EI%20want%20a%20formula%20to%20tell%20me%20if%20the%20entrance%20time%20is%20between%2022%3A00%20and%2005%3A00.%3CBR%20%2F%3EIf%20the%20answer%20to%20the%20above%20is%20yes%20-%20I%20want%20a%20%22yes%22%20as%20a%20outcome.%3CBR%20%2F%3EIt%20is%20excel%20for%20Hebrew.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1864345%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20IF%20with%20hour%20formed%20cells.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1864345%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3C%2FP%3E%3CP%3EThanks!%26nbsp%3B%3C%2FP%3E%3CP%3E1.%20Is%20%22%26gt%3B--%22%20means%20after%20and%20%22%26lt%3B--%22%20before%3F%3C%2FP%3E%3CP%3E2.%20Will%20it%20work%20in%20other%20formulas%3F%26nbsp%3B%3C%2FP%3E%3CP%3E3.%20The%20option%20you%20suggested%20does%20not%20count%20the%20exact%20time%2022%3A00%3A00%20in%20the%20range%2C%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20include%20it%20in%20(like%20%22%3D%26gt%3B%22%20in%20math)%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor
I need to make a condition:
If entrance hour is between 22:00 and 05:00...
How can I do it using if or ifs formula?
Excel 365
Thanks.
16 Replies
Highlighted

@itaytr 

I know I don't know anything (Socrates)

 

 

With your permission, if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases. This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

* Knowing the Excel version and operating system would also be an advantage.

 

Thank you for your understanding and patience

 

Nikolino

I know I don't know anything (Socrates)

Highlighted
I attached a file for example.
Waiting for a solution.
Highlighted

@itaytr 

When the admission hour is between 10:00 PM and 5:00 AM ... what should happen here?

Excel is from the Arabic or Hebrew region.

From right to left.

In your Excel sheet you only have these lines

entering hour
22:00:00
19:00:00
23:15:00
06:00:00
04:15:00

... what should become of them?

... and as already written, please state the Excel version and operating system,

if possible exactly (e.g. Excel 365 Pro or Win10-2004).

Forgive us for the circumstances, but if no precise task can be set and no precise instructions can be given, then in the best case, only an imprecise solution can be suggested to you.

 

Thanks for your patience and understanding

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@itaytr 

Just the comparison:

=OR((B5>--"22:00:00"),(B5<--"05:00:00"))
Highlighted
Hi Nikolino,
I updated the file and added a column with what I want to achieve.
I'm using excel in my android phone.
I'll try to be specific about what I want:
I want a formula to tell me if the entrance time is between 22:00 and 05:00.
If the answer to the above is yes - I want a "yes" as a outcome.
It is excel for Hebrew.
Highlighted

@Detlef Lewin

Thanks! 

1. Is ">--" means after and "<--" before?

2. Will it work in other formulas? 

3. The option you suggested does not count the exact time 22:00:00 in the range, 

Is there a way to include it in (like "=>" in math)?

 

Thanks again! 

Highlighted

@itaytr 

< means smaller than, > means greater than and -- is a type conversion from text to number because the time is set in double quotes which means Excel regards it as text.

If you want to include the lower and upper boundaries change < to <= and > to >=.

 

Highlighted

@itaytr 

As variant

=B5=MEDIAN(B5,5/24,22/24)
Highlighted

@Sergei Baklan 

Are you sure?

Your formula returns the same result only in row 5.

 

Highlighted

@Detlef Lewin 

Nope, I'm not sure. Perhaps I misunderstood what shall be true and what false:

image.png

Highlighted

@Sergei Baklan 

Well, then let's wait for the tread opener to decide.

 

Highlighted
True is when an entering time is between 22:00 to 05:00.
Highlighted
Is there a way to check with a formula if an employee worked 2 hours or more between 22:00 to 05:00.
In the file attached to this reply, Jimmy and Ronni suppose to be "TRUE" and the rest should be "False".
Highlighted

@itaytr 

And here we are with a totally different problem.

=((1-MAX(B5,22/24))+(MIN(C5,5/24)-1)+(C5<B5))>=2/24
Highlighted

@itaytr 

Since I have a great weakness for the Hebrew culture, please allow me and everyone else involved to add my thoughts.

Everything in the file, maybe it will help you to develop your project further.

 

Hope my thoughts in the file fits into your project.

 

Thank you for your patience and time.

 

Wish you a nice day / night with lots of health, joy and love.

 

Nikolino

I know I don't know anything (Socrates)

Highlighted

@Detlef Lewin 

It only to be adjusted to night shifts like start 01:00 and end 08:00