IN/OUT hours to 3 different colums (6.00AM-6.00PM, 6.00PM-10.00PM, 10.00PM-6.00AM)

Hi.

Run and search over net got me confused. Made it with IF and MEDIAN and all time got wrong numbers. Got it work when start time (IN) was less or more 6.00AM.
Anyone can help make this simple sheet? Or have right direction to me?

I just made a similar sheet for another forum member last week, and there is nothing simple about it...sorry. I best way I know is to use nested IF statements. Is this a payroll sheet? I am a little bit confused by how you are using the information. I will be happy to help more if you can tell me how you will be using the time categories or post your sheet for review. Good luck!

OK, i try explain more.
First its not payroll, just to get right working/usage hours to right place.
A1 IN time (on actual usage it never lower than 04:00AM, but can be)
B1 OUT time (on actual usage it never bigger than 11:00PM, but can be)
C1 i need to remove max 30min break time at 6:00AM-6:00PM ( break time never will be removed from another times
D1 is working/usage hours 6:00AM-6:00PM (at this i need remove break time max 30min)
E1 is working/usage hours 6:00PM-10:00PM
F1 is working/usage hours 10:00PM-6:00AM

EDIT: lets say i started at 5:00 am and ended 8:00 pm, then i need get to 10:00PM-6:00AM 1, 6:00AM-6:00PM 12 and 6:00PM-10:00PM 2

Hope its now clear

Okay very clear now thank you. I usually use a lot of hidden cells for sheets like these. Is that okay? The cell formulas become so huge they are very difficult to follow without breaking them up into several cells.

If you want to try it on your own here is how I suggest going at it. Make 6 hidden columns to calculate start and stop times for each time category. Then calculate the start and stop times for each section with IF statements...For example in the 6am to 6pm category for start time

IF(and(A1<06:00,06:00,IF(A1<18:00,A1,""))

And so on for all 6 start/stop times. Then you can subtract starts from stops in D1,E1,F1 and you will have it. (don't forget that before 6am is actually 10:00pm to 6:00am). This not easy to get right. If you want more help from me please let me know.

Actually i'm very beginner on this and i need it once. Just to verify that my usage hours are right. If you can do this on your way, it will be OK. I so confused now with that great tool EXCEL :-D

With the added IFs from Saturday and Sunday calculations this is like a 2 day project for me. It will take me some time. Are you patient? It is possible someone else knows an easier way, but I don't know of any. Would you like to try or is that to long?

Thanks to your hint for usage IF and AND. I found that i need for better results also OR.
For Saturday and Sunday check i seen somewhere on net example. I try this.
Yes, its not easy. I have now (after 3h hard work) half done. When i end it i attach example too.

Nice!!!!! Congratulation on figuring this one out. It is not an easy concept. Please let me know if you get stuck. I am happy to help. Good luck!