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

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Hiya. Thanks for the reply.

Oddly enough, the paper clip icon doesn't work for me for some reason. A small horizontal white strip appears right under it but there's no place to enter a file upload or any button for that matter.

I actually tried to edit my post previously to add a sample work sheet, but ended up having to upload images instead.

Am I possibly missing a plug in? I also tried to insert formulas but I ran into a firewall telling me I'm not allowed to post html codes.

In any case, the manage attachment link worked. Thanks! Here's a copy of a sample table.

Try this formula:

Please Login or Register  to view this content.

BTW double check the answer in L16 where the hours worked were from 11:00 PM to 7:00 AM.
Let me know if you have any questions.

Originally Posted by j4987

Thanks JeteMC,

This seems to work! My entry in L16 was indeed wrong as I ended up counting the last hour at 7am when I shouldn't have. I was actually nearly done with the my own fix using a TON of if nested statements. This formula, on the other hand, is heaps more refined and way shorter. Not really sure how it works, but it does!

Thank you! You saved my brain...

-J4

Whoops spoke too soon. An entry of a time in at 11pm / 2300 and a time out of 12mn . 2400 results in 7 hours of night shift.

I tried this:

Please Login or Register  to view this content.

It gets rid of the initial hours on a time in @ 11pm or 12pm. However, this code only gives the total hours rendered after 10pm.

To limit the max entry to 8 hours, I decided maybe a ">8" rule might apply.

Also, to account for hours rendered past 8, I thought maybe if the time logged out is greater than 600 then maybe I could apply a rule the time logged out - 600 divided by 100 to get the number of hours past 6 to subtract from "hours gone by after 10pm"

Not sure if that makes sense, but I ran into a wall when a pop up said it was too long. The code above was the last working entry that counts hours past 10pm, but doesn't account for the 8 hour limit or the actual hours rendered until 6 only.


Another Update:

I came up with this formula. It seems to be working, but I'm not sure if it's true for all values or most situations. Anyone interested in checking it out?

Please Login or Register  to view this content.

Thank you.

- J4

If the formula in post #6 is problematic try this modification to the formula in post #4:

Please Login or Register  to view this content.

Let me know if you have any questions.

I'm not seeing that issue. I just tested the file from post #3 with the start time 2100 and the end time 500 using the formula from post #7 and got a Night Shift Hours result of 7. Please upload a current copy of the spreadsheet so that we can see why you get a result of 0.