IF NOW is between specific timeframes not counting both HOUR and MINUTE - HELP PLS :)

[JEH105]

Can anyone assist with figuring out a way to write out this function to where it takes both hour and minutes into consideration when comparing the NOW time?

Details:

This is the formula I have under John Smith 1 row:

=IF(AND(HOUR((NOW()))>=HOUR($E14),MINUTE((NOW()))>=MINUTE($E14))+AND(HOUR((NOW()))<=HOUR($F14),MINUTE((NOW()))<=MINUTE($F14)),"Yes","No")

FYI:
John Smith = Row 13
John Smith 1 = Row 14
John Smith 2 = Row 15
John Smith 3 = Row 16
John Smith 4 = Row 17
John Smith 5 = Row 18

Issue:

My issue with this formula, is that if the current time is 9:00 am or anytime before the 30 minute mark, it will show as "No", even if it's past 6:30 am. I'm pretty sure it's because of the MINUTE function, but I'm not sure how else to write it.

Any help is truly appreciated! Thank you!

 

[Fluff]

How about

=IF(AND(TEXT(NOW(),"hh:mm")>=TEXT(E14,"hh:mm"),TEXT(NOW(),"hh:mm")<=TEXT(F14,"hh:mm")),"Yes","No")

 

[JEH105]

How about

=IF(AND(TEXT(NOW(),"hh:mm")>=TEXT(E14,"hh:mm"),TEXT(NOW(),"hh:mm")<=TEXT(F14,"hh:mm")),"Yes","No")

Yes! Thank you!

I have one more question. If I wanted the formula to indicate Yes, but only if the timeframe is 1.5 hours before the End Time, do you know how I would do that?

Example:
John Smith 1 ends time at 3:00 pm, I would want the formula to indicate No 1.5 hours before 3:00 pm (1:30 pm).

 

[Fluff]

How about

=IF(AND(TEXT(NOW(),"hh:mm")>=TEXT(E14,"hh:mm"),TEXT(NOW(),"hh:mm")<=TEXT(F14-TIME(1,30,0),"hh:mm")),"Yes","No")

 

[JEH105]

How about

=IF(AND(TEXT(NOW(),"hh:mm")>=TEXT(E14,"hh:mm"),TEXT(NOW(),"hh:mm")<=TEXT(F14-TIME(1,30,0),"hh:mm")),"Yes","No")

Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.