Need a little help retrieving night shift through numerical values instead of time.

Hello. I have a little problem with the formula I made that calculates the number of night shift hours rendered based on a night shift window of 10pm-6pm.

A little background on the spreadsheet: For our purposes I wanted the logic formulas to return whole numbers and facilitate quick entries independent of date. I'm no expert, so my formulas will be quite cluttered and simplistic. What I've done is have the values 100-2400 be representative of the hours in a day; 100 being 1am, 200 as 2am, 1,500 as 3am, 2,400 as 12 midnight, etc. I've limited the use to just whole hours; no minutes or seconds (a minor limitation for my purposes).

I have two columns a time in and a time out, and I created simple formulas to calculate the total hours rendered and number of hours that are considered over time.

\1

\1

Formula for total hours:

Where the column C is representative of the Time In while D is Time Out.

The formula for hours over time follows the logic that any hour rendered past 8 hours is considered over time hence the formula:

So basically total time rendered minus 8 hours is over time.

My issue comes with calculating night shift hours. I just want to know how many hours of the total hours calculate falls under the Night Shift time frame between 10pm and 6am.

I came up with this formula:

Whereas C again is the time in, and E is the formula for total hours rendered. What happens here is the formula checks for the applicability of night shift, which starts past 10pm or 2,200.

It works okay for some entries, but entries that have time ins/outs containing 2,300 or 2,400 (11pm or 12pm) are not displaying the number of hours I want to reflect. I understand that this could be a mathematical problem with the formula. The calculations are correct but the logic is false. I have a feeling this is due to an incomplete identification of night shift.

I was wondering if there was any logic i could add that may help remedy the problem.


Thank you