Formula comparing cells etc

[jtzako]

I have this formula that currently checks a cell with a time in it (i.e. 00:00 or 13:00) vs another cell with a time and changes another cell value based on that.
Example I'll use has the formula in Q14. It compares Q2 vs L14 and also Q2 vs M14. L14 is shift start time, M14 is shift stop time. Q2 is the column where midnight (00:00) starts.

In order for other parts of my sheet some cases the M14 cell actually equals "1/1/1900 1:00 AM" for (for the shifts that continue to next day)

This causes the formula to think 00:00 is greater than 01:00 for some reason and it marks the field with a 0.
Formula:

=IF(AND(L14<=Q2,Q2<M14),1,0)

I need to modify this formula so that if someone works from 16:00 one day to 01:00 the following morning it still marks the field with a "1."

I think I need an "Or" in the formula but I'm not sure how to put it in there. I still need it to put in a "0" if none of the conditions are met.

 

[Scott Huish]

What is the point of this?

Are you just subtracting time? But it's not clear if you're using dates in the cell or not. if not this should do it:
=MOD(M14-L14,1)

Otherwise I would say your sample has incorrect data in it, as 1/1/1900 does not seem like a reasonable stop date. Can you not fix your data?

If, however it says 1/0/1900 1:00 AM, this would need to be formatted to not include dates, if you are not using them.