calculating deductions

I need to calculate the total number of minutes an employee is late per day, and then add that total for a weekly one.

I already have it setup so when i enter their in and out time it calculates how many hours they worked. I want to use the same in and out date ( i do not want to have to enter it again), enter the weekly schedule, and get the daily and then weekly totals.

My schedules are made in excel, so i am thinking i can just copy it over, and then use some sort of formula to calculate the total number of minutes late?

----------------------------------

Additionally i would like excel after calculating the weekly total pull from a time deduction table, and dock their hourly wage. ie. employee normally makes $5.00. employee was late a total of 30 min for the week. (time deduction table says to doc $0.50 an hour) so for that week the employee would only make $4.50 a week.

I have looked around, and can't find too much on how to do something like that. Feedback would be really appreciated.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

they are docked that much an hour

huh? Can you put that in words for people who don't know anything about the subject matter?

Sorry, English is not my first language. I've not come across the term "docked" in that kind of situation. What does "They are docked" mean?

Also, looking at your file: could it be that the data in Column E is wrong? Column A is AM and column E is PM. That does not make sense, or does it?

So, in your sample data, they are supposed to arrive at 6 am but arrive at 6 pm, and then leave at 2 pm?

I take it that the PM in column E are typos.

If so, you can do this:
in H7 and copy down

=A7-E7

Format H7 to H12 as general, so as to avoid confusion.

In H13 =SUM(H7:H12)

In A18 to A24
0
10
15
25
30
45
60

Then calculate the wage deduction with

=VLOOKUP(MINUTE($H$13)+HOUR($H$13)*60,$A$18:$B$24,2,1)

see attached.

SUM(H7:H12) will not work because i do not want to include the time they arrive early in the total. I only want the time they arrive late.

Also i am not understand what you are saying about the wage reduction

please do not quote whole posts. it's just clutter.

I guess what you call "docking" is what I call deduction. It's the value that you arrive at if you look at the number of tardy minutes in your table starting in row 17

In my previously attached example, the total was 60 tardy minutes, so the Late Deductions will be as in row 24, which is "mim wage = 7.25"

If you don't want to credit early arrival, then change the formula in H13 to

=SUMIF(H7:H12,">0")

cheers

put this in B3

Please Login or Register  to view this content.

see attached.

By the way, thanks for the attempt at giving me reputiation, but unfortunately, you hit the wrong button and gave me negative rep. It can be fixed, though.