#### NathanJ

##### Board Regular
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
ABCD
1
2Sunday1/2/2005BREAKTIME
3#ofBreaks210
4IN8:00AM
5OUT10:00AM2.00BREAK
6IN10:06AM
7OUT12:00PM1.90
8IN1:00PM
9OUT2:00PM1.00BREAK
10IN2:04PM
11OUT3:10PM1.10
12IN3:20PM
13OUT5:00PM1.67
14IN
15OUT0.00
16IN
17OUT0.00
18IN
19OUT0.00
20IN
21OUT0.00
22IN
23OUT0.00
24IN
25OUT0.00
26IN
27OUT0.00
28
Sheet1

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
IS this not possible?

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

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

Try this too:
Book3
ABCD
2Sunday1/2/2005BREAK TIME
3# of Breaks210
4IN8:00 AM
5OUT10:00 AM2.00BREAK
6IN10:06 AM
7OUT12:00 PM1.90
8IN1:00 PM
9OUT2:00 PM1.00BREAK
10IN2:04 PM
11OUT3:10 PM1.10
12IN3:20 PM
13OUT5:00 PM1.67
14IN
15OUT0.00
16IN
17OUT0.00
18IN
19OUT0.00
20IN
21OUT0.00
Sheet1

 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")

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.

Book1
ABCDE
1
2SundayTotalBreakTime
3#ofBreaks1.000
4IN8:00AM
5OUT1:10PM5.17
6IN1:22PM
7OUT5:00PM3.63
8IN
9OUT0.00
10IN
11OUT0.00
12IN
13OUT0.00
14IN
15OUT0.00
16IN
17OUT0.00
18IN
19OUT0.00
20IN
21OUT0.00
22IN
23OUT0.00
24IN
25OUT0.00
26IN
27OUT0.00
Sheet1

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....

Replies
0
Views
457
Replies
0
Views
516
Replies
5
Views
707
Replies
8
Views
518
Replies
5
Views
1K

1,203,396
Messages
6,055,158
Members
444,766
Latest member
bryandaniel5

### 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.

### Which adblocker are you using?

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

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