# Night working hours

Topic Labels: Formulas
532 2
cancel
Showing results for
Did you mean:
4 - Data Explorer

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?

2 Replies 2
11 - Venus

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!

4 - Data Explorer

This is it!! Thank you very much!