Excel formula for overnight hours within a specific time period

MellieD

New Member
Joined
Jan 20, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I need help to create an Excel formula to see how many overnight hours have been worked during a particular period for billing purposes. Our employees’ clock in and out at various times and I need to exclude the time outside of the hours of 11pm to 6am. We pay our employees for all the time they work but we can only bill our customer for time worked between 11pm and 6am the next day. Below is an example of several time punch scenarios to calculate how much we can bill to our customer. I’ve tried several IF, MOD and TIMEVALUE formulas but nothing seems to work due to the extra complexity of calculating time over two day period. For instance, the perfect scenario is in row 2 where employee clocked in at 11pm and clocked out at 6am. 7 hours is billed to the customer.

In row 3, the employee clocked in 5 minutes early and clocked out 10 minutes later. We will pay the employee 7 hours and 15 minutes but only bill the customer 7 hours.

Row 4 would be billed at 30 minutes and so on. Row 11 we would not bill the customer for any hours due to the entire time being outside of the 11pm to 6am window.

I know these examples are somewhat simplistic in their clock in/clock out nature. I would prefer to stay away from VBA as I don’t understand it and have no experience with it. Any suggestions on formulas I can use to cover all of these scenarios? Total time worked and time only between 11pm and 6am?
Thank you

1642715094946.png
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hi MellieD,

The tricky part is figuring out what the dates are for the 11pm start and 6am end. This formula assumes if the Clock In is after 12 noon then you're looking for 11pm the Date In to 6am the next day. If the Clock In is before 12 noon then you are looking at 11pm the day before Date In to 6am of the Date In.

MellieD.xlsx
ABCDE
1Date InClock InDate OutClock OutOverlap with 11pm to 6am
212/31/202123:001/1/20226:007:00
312/31/202122:551/1/20226:107:00
41/1/202223:151/1/202223:450:30
51/1/202222:501/2/20225:006:00
61/1/202223:021/2/20226:156:58
71/1/202223:051/2/20225:456:40
81/2/20220:001/2/20226:006:00
91/2/20221:001/2/20224:003:00
101/2/20221:001/2/20226:305:00
111/2/202219:001/2/202222:000:00
1st
Cell Formulas
RangeFormula
E2:E11E2=MAX(MIN(A2+TIME(6,0,0)+(IF(B2>TIME(12,0,0),1,0)),C2+D2)-MAX(A2+B2,A2+TIME(23,0,0)+(IF(B2>TIME(12,0,0),0,-1))),0)
 
Upvote 0
Solution
Hi Toadstool,
Good point about taking the dates into consideration for the 11pm start and 6am end. I totally overlooked that aspect.
Thank you for this solution. It works perfectly.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top