Calculating a Clock-Out Time depending on Hours/Days worked so far for the Week

[mgreene]

I'm not sure if this is possible, I've tried a few different things but the formula is too complex for my knowledge. Basically, I would like to look at my employees' hours worked for the week, the number of days they have left to work that week, and determine what their clock out time should be. For a very simple example, if my goal is to keep everyone at 40 hours or under, and I have an employee who's worked 2 out of 5 days and already has accrued 16 hours, then the formula would take 40-16 and divide the remaining 24 hours up over the three days they have left work. I put the table below as Dispatcher 1. I also need to calculate the projected clock-out factoring in a half-hour lunch being taken that day. This way, no matter when they clock-in, I can easily look at this table and it will tell me how many hours they have left to work before they get to 40 and, if divided evenly, what time they should be clocking out to keep their schedule as regular as possible. I know what I want the formula to do, I am just having trouble converting time from a general value (i.e. 20 hours) to a time of day (17:00). I am using Windows 7 and Microsoft Excel 2007 if that helps.

This ideally is a spreadsheet that I use on a daily basis, week to week. In the table below, I would be opening this spreadsheet on Wednesday morning, inputting their hours worked thus far in order to see how much time they have left (before going over 40) and spreading the remaining time evenly over Wednesday and Thursday. I didn't use formulas for the table below, I did the math in my head. It's easy to do in an example like the one below, but when you're looking at 20+ employees who are all clocking in at different times it makes it a little more difficult.
For the days remaining column, I'm using this formula "=COUNTBLANK(B7:H7)-2" because everyone is on a 5-day work week.

Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L
Dispatcher 1
Dispatcher 2

<COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0"><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: center"]SUN[/TD]
[TD="align: center"]MON[/TD]
[TD="align: center"]TUE[/TD]
[TD="align: center"]WED[/TD]
[TD="align: center"]THU[/TD]
[TD="align: center"]FRI[/TD]
[TD="align: center"]SAT[/TD]
[TD="align: center"]Hours Worked[/TD]
[TD="align: center"]Days Remaining[/TD]
[TD="align: center"]Clocked In[/TD]
[TD="align: center"]Projected ClockOut[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]8.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"]8.00[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]24.00[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]16:30[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]9.40[/TD]
[TD="align: center"]9.20[/TD]
[TD="align: center"]10.60[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]OFF[/TD]
[TD="align: center"]29.20[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]8:00[/TD]
[TD="align: center"]13:54[/TD]

</TBODY>

Dispatcher Hours​

The end result that I'm looking for is a formula for Column L that will take hours worked, days remaining, clocked in time, and a half-hour lunch into account and then will give me a clock out time for the current day. If I'm unclear about any of the above please ask questions. I know excel can do this I just need to find someone smarter than me, that's why I'm here!

 

[Snakehips]

mgreene,

Try this.......

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K	L
22		Sun	Mon	Tue	Wed	Thu	Fri	Sat	Hrs Worked	Days Remaining	Clocked In	Projected Clock Out
23	Dispatcher 1	08.00	08.00	08.00			Off	Off	24	2	08.00	16.30
24	Dispatcher 2	09.40	09.20	10.50			Off	Off	29.2	2	08.00	13.54
Sheet1

Cell Formulas
Range	Formula
J23	=COUNTBLANK(B23:F23)
L23	=K23+TIME(TRUNC(((40-I23)/2)+0.5),((((40-I23)/2)+0.5)-TRUNC(((40-I23)/2)+0.5))*60,)

Hope that helps.

 

[Terredean111]

Hi,

I would be happy to help you. I too deal in Attendance tracking system. I would be happy to help you.

Thanks
Terredean