Split Night Shift into Hours per Day

[Julien_Charpent]

Hello, 

I am trying to calculate night working hours for my employees and I can't figure out the right script. 

Each day, each employee registers its time hours in a table through 4 fields : 

(Shift1-Start) ; (Shift1-End) ;  (Shift2-Start) ; (Shift 2-End) 

With those fields, I am able to calculate duration of work each day, and through a pivot table, I am able to calculate the sum of those hours, compared to the "theorical" hours of each week of the year, hence calculate the real OT. 

Most of my employees start at 6:00, but some times some of them start a little bit earlier. And sometime they have night work starting at 10 pm and ending the day after at 4 am. In this case, we register 10 pm to midnight on Day 1 and the then midnight to 4 am on Day 2.

What I want to calculate now, is the hours worked at night, that means those who are worked between 9 pm and 6 am. 

Would you have any script in mind?  

Thank you in advance! Julien. 

[Sho]

Hello @Julien_Charpent ,

How about a formula like this?

MAX(Shift1_End,75600)-MAX(Shift1_Start,75600)+
MIN(Shift1_End,21600)-MIN(Shift1_Start,21600)+
MAX(Shift2_End,75600)-MAX(Shift2_Start,75600)+
MIN(Shift2_End,21600)-MIN(Shift2_Start,21600)

 I think this would probably be enough to calculate all the patterns.
I could be wrong, please check!

[Julien_Charpent]

This is it!! Thank you very much!