advanced formulas

[NathanJ]

this is an employee time card

i want to automate the process of evaluating the paid breaks
you get 0,1,2 breaks in a day each break is paid up to 10 min.
i will manual decide if breaks will be awarded or not if only a lunch is taken then # of breaks will be 0 and so on... however i would like the computer to figure out what "out , in " combanation is the break or breaks and add them up and also indicate which "out, in" were chosen to be used as breaks.... i hop this is clear. below is the idea without the correct formulas.

Book2
	A	B	C	D
1
2		Sunday	1/2/2005	BREAKTIME
3		#ofBreaks	2	10
4	IN	8:00AM
5	OUT	10:00AM	2.00	BREAK
6	IN	10:06AM
7	OUT	12:00PM	1.90
8	IN	1:00PM
9	OUT	2:00PM	1.00	BREAK
10	IN	2:04PM
11	OUT	3:10PM	1.10
12	IN	3:20PM
13	OUT	5:00PM	1.67
14	IN
15	OUT		0.00
16	IN
17	OUT		0.00
18	IN
19	OUT		0.00
20	IN
21	OUT		0.00
22	IN
23	OUT		0.00
24	IN
25	OUT		0.00
26	IN
27	OUT		0.00
28
Sheet1

 

[NathanJ]

IS this not possible?

 

[Oaktree]

This isn't exactly what you're asking, but I think it might be useful for you:

If you calculate the number of minutes the person was on break (i.e. between clocking out and clocking in), you could pay him/her for the first x minutes of break [after all, if s/he took 2 three hour breaks, you wouldn't be happy, even if they were allowed 2 breaks ] where x is 10 minutes * the number of breaks they get + some amount of time for lunch. I'm guessing that your "minutes of breaks" formula could be automated by =10*[last punch out - first punch in]/(hours_to_earn_1_break/24).

This formula calculates the number of minutes on break:
=((C13-C4)-(SUMIF(B4:B13,"out",C4:C13)-SUMIF(B4:B13,"in",C4:C13)))*24*60

 

[Oaktree]

... the above assumes your times are time values, not text (i.e. formatted as h:mm AM/PM)

 

[Seti]

Try this too:

Book3
	A	B	C	D
2		Sunday	1/2/2005	BREAK TIME
3		# of Breaks	2	10
4	IN	8:00 AM
5	OUT	10:00 AM	2.00	BREAK
6	IN	10:06 AM
7	OUT	12:00 PM	1.90
8	IN	1:00 PM
9	OUT	2:00 PM	1.00	BREAK
10	IN	2:04 PM
11	OUT	3:10 PM	1.10
12	IN	3:20 PM
13	OUT	5:00 PM	1.67
14	IN
15	OUT		0.00
16	IN
17	OUT		0.00
18	IN
19	OUT		0.00
20	IN
21	OUT		0.00
Sheet1

[edit] Problem seeing formulas:

Column C: [c5] =IF(A5="OUT",24*(B5-B4),"")
Column D: [d5] =IF(AND(A5="OUT",B6>0),IF(1440*(B6-B5)<$D$3,"BREAK",""),"")

Cell C3: =COUNTIF(D4:D21,"BREAK")

 

[NathanJ]

wow that is almost it i did not konw about the if(and thing thats neet

my problem is if i allow a 10 min break and they person is clocked out for 12 min they should get the 10 min the error is shown below
i formated it a little different because i will enter the number of breaks and then the automation goes from there adding up the total break time not to exceed 10*numb of breaks.

thanks for your help.

Book1
	A	B	C	D	E
1
2		Sunday		TotalBreakTime
3		#ofBreaks	1.00	0
4	IN	8:00AM
5	OUT	1:10PM	5.17
6	IN	1:22PM
7	OUT	5:00PM	3.63
8	IN
9	OUT		0.00
10	IN
11	OUT		0.00
12	IN
13	OUT		0.00
14	IN
15	OUT		0.00
16	IN
17	OUT		0.00
18	IN
19	OUT		0.00
20	IN
21	OUT		0.00
22	IN
23	OUT		0.00
24	IN
25	OUT		0.00
26	IN
27	OUT		0.00
Sheet1

 

[NathanJ]

i tried to change the

=IF(AND(A5="OUT",B6>0),IF(1440*(B6-B5)<$D$3,"BREAK",""),"")
to

=IF(AND(A5="OUT",B6>0),IF(1440*(B6-B5)<$D$3,((b6-b5)*1440),10),"")


but this acted weird and would exceed the limit of brakes i placed....