Formula comparing cells etc

jtzako

Board Regular
Joined
Jan 11, 2007
Messages
90
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,104
Messages
5,599,753
Members
414,335
Latest member
cleverExcel

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top