Formula comparing cells etc

jtzako

Board Regular
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:
Code:
``=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.

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Scott Huish

MrExcel MVP
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.

Replies
1
Views
25
Replies
2
Views
123
Replies
9
Views
313
Replies
3
Views
107
Replies
4
Views
162

1,128,087
Messages
5,628,593
Members
416,326
Latest member
NinaChristal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back