Time Card auto calculations

[jjulian91]

I am creating a spreadsheet which I have attempted to make as autonomous as possible.

We have a cell which is dedicated for tracking over time hours and double time hours. Our front office software does track our work hours however we can have multiple entries per day which can lead to multiple rows being considered for the same payroll hours.

Date	Employee name	Notes	Customer Name	Job Number	Job Name	Hours
2/10/17	doesn't matter	doesn't matter	doesn't matter	1	doesn't matter	5
2/10/17	doesn't matter	doesn't matter	doesn't matter	2	doesn't matter	4
2/11/17	doesn't matter	doesn't matter	doesn't matter	1	doesn't matter	13
2/13/17	doesn't matter	doesn't matter	doesn't matter	3	doesn't matter	9
2/14/17	doesn't matter	doesn't matter	doesn't matter	4	doesn't matter	8
2/15/17	doesn't matter	doesn't matter	doesn't matter	5	doesn't matter	8
2/16/17	doesn't matter	doesn't matter	doesn't matter	6	doesn't matter	9
2/17/17	doesn't matter	doesn't matter	doesn't matter	7	doesn't matter	8

This is a sample of what we deal with. Here is where things get hairy -- we are required to pay OT and DT whenever someone works more than 8 Hours a day AND anything over 40 hours/week. The first two rows would return a 1 in OT, the third would return 4 in OT and 1 in DT, the fourth 1 in OT, then the last two rows would return a 9 and 8 both in OT.

I have tried to use arrays/sumifs/sumif/match but cannot get the formula to actually return logical numbers.

any help would be great!

[David A Coop]

I'm a bit confused! The dates shown are Friday to Friday, so the last two rows going into OT seems wrong.

What is your working week?

Any penalty on a Saturday? (Here in Australia, work on Saturday usually has a loading of some sort).

Do you want three columns - Normal Hrs, OT and DT against each row?

You have "doesn't matter" under employee name. Does this mean you will have one sheet per employee?

Will you have multiple work weeks on the same worksheet?

I think that's all for now - I just need a little more info before a good response can be provided.

Finally, can the solution use VBA, or do you want formulas only?

Regards,

David

[jjulian91]

I'm a bit confused! The dates shown are Friday to Friday, so the last two rows going into OT seems wrong.

What is your working week?

Any penalty on a Saturday? (Here in Australia, work on Saturday usually has a loading of some sort).

Do you want three columns - Normal Hrs, OT and DT against each row?

Regards,

David

So thats where things get difficult. Work week is 7 days and pay period is 1-15 and 15-end (plumbing company that provides emergency services).so for any given 7 days anything over 40 hours is ot and over 60 is dt. And any day over 8 is ot and over 12 is DT.

As of now I have a total hour column which is base rate*hours then ot which is tallied off how many hours were ot (.5*base rate since the rest of the time is already counted in total hours) then dt (over 12 hrs in a day or 60 hours in a 7 day period) (base *1). So even if they work 66 hours they get base rate in one column .5 rate for 20 hours in another and another base*hours above 60. But the daily break down with multiple daily entries is what makes it even more difficult.


Yes one per employee.

Ideally looking at just formula not very experienced with vba--i can post exactly what I'm working off of to give a better idea if 100% necessary.

[David A Coop]

I added a few more questions.

Sorry, I didn't expect you to get back so fast!

DAC

[JohnTopley]

Try

for OT

=IF(SUM($G$2:G2)>40,MEDIAN(0,SUM($G$2:G2)-40,G2),IF(COUNTIF($A$2:A2,A2)=1,MIN(4,MAX(0,SUMIF($A$2:$A$9,A2,$G$2:$G$9)-8)),0))

For DT

=MAX(0,G2-12)

See attached..

[jjulian91]

Try

for OT

=IF(SUM($G$2:G2)>40,MEDIAN(0,SUM($G$2:G2)-40,G2),IF(COUNTIF($A$2:A2,A2)=1,MIN(4,MAX(0,SUMIF($A$2:$A$9,A2,$G$2:$G$9)-8)),0))

For DT

=MAX(0,G2-12)

See attached..

So thats almost spot on which is very impressive. The only 2 issues are the start of a new week because our pay periods are often 2 weeks + some days. So the 40 hour counter resets on day 8. Also the OT for beyond a 40 hr week is only taken off standard hours. So working 3 12 hour days only takes 24 hours off the 40 hour week as opposed to 36 hours, because the additional 12 hours (4 per day) are already added into the calculation or else the ot/dt has thw ability to double dip.

Thanks for the help!

[JohnTopley]

I have modified the results: I don't know how to re-set on 8th day as there is no parameter which indicates the Start of a week. You need to add a column which has "Week number" so that could be used as a trigger for the re-set.

[jjulian91]

I have modified the results: I don't know how to re-set on 8th day as there is no parameter which indicates the Start of a week. You need to add a column which has "Week number" so that could be used as a trigger for the re-set.

John I totally forgot about that -- we do have a few other sheets in the calculation. let me screen shot what I am talking about.
spreadsheet.jpg

sheet 2.jpg



So the first page is where the sum of the columns will export to, the pay period range is where it will say how many days in a pay period.

[JohnTopley]

The image adds nothing to my understanding of what further is required.

[jjulian91]

The image adds nothing to my understanding of what further is required.

There are 2 images. -- It shows we have 2 fields for beginning pay period and end pay period which will be where the reference would be to when the period starts when 7 days is over etc. Do you have a suggestion which I need to add to make it easier to update or will the 2 dates suffice?

[JohnTopley]

Much easier if you posted a file with these.

The dates on the image are 2/1/2017 and 2/3/2017 (in mm/dd/yyyy) so 3 days ??

It does not matter what dates you choose or what calculation you use for week number (I used Excel's WEEKNUM function).

If the formula I gave you works then you just need to add a date(s)/ (a) week number formula into the table.