• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hours worked within given time frame

M

MJSooner

New Member
Hi,

I have a column with a time worked start time and a column with a time worked end time, both in the 24 hour (military time) format. I am trying to calculate the amount of time in that shift that falls between the hours of 1800 and 0600 (6pm to 6am) but it's above my Excel pay grade. Is this doable? Thank you.
 
Code: 
=IF(AND(A2>=TIME(18,0,0),B2<TIME(6,0,0)),B2-A2,IF(OR(A2>=TIME(18,0,0),A2<TIME(6,0,0)),TIME(6,0,0)-A2,0))


Assuming your start time is in cell A2 and end time is in cell B2, you would place this formula in the cell where you want the result to appear. Adjust the cell references as needed for your specific setup.
 
You must log in or register to reply here.
Back
Top