PAYROLL ON EXCEL

[lancemeroniuk]

Hi all.. I have a payroll spreadsheet that I need help with. I hve the 31 days of the month. What I need to do is caculate every seven days, if the total of the 7 days is greater than 60 hrs, then overtime is calculated. My biggest problem is that these 7 days spill into the next month. IE: I have to caculate say day 28,29,30,31,01,02,03 this month, for example. Then depending where the next months starts and end, the 7 day period needs to move.

As of now, I manually move the 7 day blocks around every month. Is there anyway to formulate a solution so that I do not have to keep reworking this sheet?

This spread sheet is used in a trucking company, where over time is calculated after 60 hours worked from Sunday to Sunday.

I hope I've explained this clear enough...

Thanks inadvance


Lance

 

[chrisaroundtown]

Lance,

I'd probably use a sumif formula, Assuming that the headers of your table are the dates. Let's say your table looks like this starting from cell A1.

29/12/03 30/12/2003 31/12/2003 01/01/2004 02/01/2004
12 10 20 15 20
.....

Then where you do the calculation it looks like this in cell A10 for the week commencing
29/12/03
=sum(sumif(1:1,A10,2:2),sumif(1:1,A10+1,2:2),sumif(1:1,A10+2,2:2),sumif(1:1,A10+3,2:2),sumif(1:1,A10+4,2:2),sumif(1:1,A10+5,2:2),sumif(1:1,A10+6,2:2))

Let me know how you go.

Chris.

 

[lancemeroniuk]

Thanks Chris...

Can you further explain the formula abit for me... the 1:1 ??? I've not seen this before..

The formula that I use is a sumif as well but I calulate the 7 day block after sum=<60 then - total to get the overtime. I don't have the exact fomula here but I will get it and post it later..

Thanks

Lance

 

[chrisaroundtown]

No worries,

The format is

=sumif(range,criteria,sum_range)

What I gave you was

=sumif(1:1,A10,2:2)

It basically applies a condition to what you want to sum.
1:1 is a range refering to the entire row number 1, if you wanted a column A:A refers to the whole A column.

Let me give you this starting from cell A1.
a 1
b 2
a 4
c 2
a 6

=sumif(A:A,&quot;a&quot;,B:B)
This formula searches column A (A:A) and everytime it finds &quot;a&quot; it adds up the value next to it in column B (B:B). So going down the list it first finds &quot;a&quot; with a value next to it of 1, then it finds &quot;a&quot; with a 4 and then &quot;a&quot; with a 6. The sum of these being 11. If you change the &quot;a&quot; in the formula to &quot;c&quot; your answer will be 2.

So in your formula
=sumif(1:1,A10,2:2)
you are searching row 1 looking for the value in A10 (the date of the Monday), everytime it finds a match it will add the value in row 2. As you would have one day for each column it will only find one value. Therefore the forumla is repeated but this time it adds 1 to the date it's searching for hence =sumif(1:1,A10+1,2:2), now it's looking for the Tuesday. Reapeating this for each day of the week then adding them up will give you a total for the 7 days starting from the Monday.

After this take 60 and you will get the number of overtime hours.

By the way, what is a truck driver worth these days?

Chris

 

[SkipVought]

Lance,

You don't need to worry about 28, 29, 30 or 31 days per month.

Dates are just numbers -- like today is 38001 (38000 days after 1/1/1900).

So what your sheet needs is a cell that is the BEGINNING date, and then all subsequent dates are formulas -- previous date + 1.

So ALL your dates for the year could be in a single column with your truckers Name and trucker Hours. You will need a column for Week

=INT((A2-1)/7)

where date is in col A

Then is you use a PivotTable, for instance, trucker name in the ROW AREA, Week in the COLUMN AREA and Hours in the DATA AREA, you can see a complete picture of each trucker's actual hours and calculate accordingly.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[lancemeroniuk]

Thanks Chris....

I see now... thanks

What's a truck driver worth?? Depends Can or USA? In Calgary Alberta where I am.... typically a starting drivers starts at 10.00 - 12.00$ / hour for local stuff. Long haul... 20 - 36 cents / mile. average 10,000 miles / month.

Thanks for the help. I'll try to set the sheet up again and see what happens. I'll have to link this table to the table that does the full calculation for paryroll...


Thanks.


Lance

 

[klmorgan]

Just a thought

You could use the WEEKNUM feature in Excel to identify the week number of each date and then calculate the wage hours using the week number.

I think that you can choose the start of the week to be a Sunday or a Monday but if neither suits you (ours starts on a Saturday) you can introduce a second date from the actual one ie. Date+1 or 2 or 3 to fool the Weeknum to what you want

We have used it for orders placed in a week etc

Regards

Keith

P.S. You may have to switch on an addin to get weeknum to work I seem to remember that not everyone could use the sheet when we first started using it and it was due to them not having an option swiched on

 

[klmorgan]

Another thought

If you used thee Weeknumber approach, and did a pivot table over the data you could have employee in the row, weeknumber in the column and sum of the hours in the data.

you would then identify all people > 60 hours

Keith