Formula

Highlighted
Occasional Visitor

Hello. Hope you are keeping well and safe. I need help with a formula to calculate the total amount of time worked within a time window please.

 

In Column “B” I have my Clock In time

In Column “C” I have my Clock Out time

In Column “D” I have a working formula for my total hours worked for that shift

In Column “J” I need to have a formula for my total Night hours worked within 22:00 to 06:00.

 

The time window for night hours is between 22:00 and 06:00. However, my clocking in and out times (“B” and “C”) often fall around of this window so I need to calculate the total amount of hours worked only within this window for Column “J” please.

 

Example Row 18: Date – Time In – Time Out – Total hours at worked – Time for break – total hours to be paid – Blank – Blank – Night hours worked

08/01/2020

18:40

00:18

5: 38

00:15

5: 23

16:52

 

 

0:00

 

 

                 

 

1 Reply
Highlighted

@KJAD5920 

Formula to calculate overnight could be

=MOD(C1-B1,1)