Basic Time Card Calculation

[jacob42685]

I created a calculator where I can enter in a total number of worked ours for a day and add each day. I would like to add a cell which will tell me what day and time a person will need to leave by to avoid overtime (any hours/mins over 40 hours). Below is what I currently have setup:

I want cell C11 to display the day of the week and time a person would need to leave by to avoid going over 40 total hours for the week. I would assume I would have to put in that persons schedule, but I'm not sure exactly how it would have to be setup.

 

[Matt Rogers]

Hi Jacob,
welcome here.

Try this:

Excel Workbook
	A	B	C
1		Working hours:	40:00
2	Day	Hour Daily Total	Hour Running Total
3	Monday		0:00
4	Tuesday	07:30	7:30
5	Wednesday	08:19	15:49
6	Thursday	08:16	24:05
7	Friday	08:16	32:21
8	Saturday	06:47	39:08
9	Sunday
10		Total:	39:08
11		+ or -	-00:52
Sheet

 

[MikeWx]

Welcome Jacob,

You are correct that some scheduling information is needed. In my example below I have added a start and end time each day. My assumption is the person either gets no lunch break or is paid for their lunch break.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 80px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 106px"><COL style="WIDTH: 127px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Day</TD><TD>Start</TD><TD>Finish</TD><TD>Hour Daily Total</TD><TD>Hour Running Total</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Monday</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">0:00:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Tuesday</TD><TD style="TEXT-ALIGN: right">7:30</TD><TD style="TEXT-ALIGN: right">15:00</TD><TD style="TEXT-ALIGN: right">7:30</TD><TD style="TEXT-ALIGN: right">7:30:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Wednesday</TD><TD style="TEXT-ALIGN: right">7:30</TD><TD style="TEXT-ALIGN: right">15:49</TD><TD style="TEXT-ALIGN: right">8:19</TD><TD style="TEXT-ALIGN: right">15:49:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Thursday</TD><TD style="TEXT-ALIGN: right">7:30</TD><TD style="TEXT-ALIGN: right">15:46</TD><TD style="TEXT-ALIGN: right">8:16</TD><TD style="TEXT-ALIGN: right">24:05:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Friday</TD><TD style="TEXT-ALIGN: right">7:30</TD><TD style="TEXT-ALIGN: right">15:46</TD><TD style="TEXT-ALIGN: right">8:16</TD><TD style="TEXT-ALIGN: right">32:21:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Saturday</TD><TD style="TEXT-ALIGN: right">7:30</TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">32:21:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD>Sunday</TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">Total</TD><TD></TD><TD></TD><TD></TD><TD style="TEXT-ALIGN: right">32:21:00</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD>Saturday 15:09</TD></TR></TBODY></TABLE>


Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Cell D3 has the following formula copied down through D9:

=IF(C3="","",C3-B3)

Cell E3 has the following formula copied down through E9

=SUM($D$3:D3)

Cell E11 is blank until the sum of the daily hours totals exceeds 31 hours. When that happens, it determines the next day and how long after the starting time of the next day the person can work until they reach 40 hours.

=IF(SUM(D3:D9)>31/24,INDEX(A3:A9,MATCH(9.9999E+307,D3:D9,1)+1)&" "&TEXT(INDEX(B3:B9,MATCH(9.9999E+307,D3:D9,1)+1)+(40/24)-SUM(D3:D9),"hh:mm"),"")

Is this what you are looking for?

Mike