Excel Time Sheet - Bi-Monthly time calculating overtime...headache

[ttam]

I'm building an Excel program to replace employees just using MS Word to submit time sheets. We are paid on the 15th and the last day of the month. Our time periods run from the 11th through 25th and 26th through 10th. Time sheets are due on the last day of the pay period.

I am working on an Excel which each employee will fill out (on a shared network drive). My goal is to have our HR person open a master time sheet that shows names of each employee and the hours for that pay period. No time calculations are needed; we just enter (in decimal format) how many hours we work each week.

I have built this program and it's quite complex, but I'm having some issues. Most pay periods have 15 days. Some have 14 and more have 16. This causes my totals to be off. I'm attaching the Excel 2007 file so you can see what I mean (that's the only way you'll be able to).

I'm open for recommendations, even a PHP-based timesheet app as long as it will allow users to just go in at the end of the pay period and enter all the time in (without IN/OUT times). We need it to be simple for EUs to enter.

Don't worry about how it looks; it'll be cleaned up (cols hidden, etc) when it's ready.

2 things I'm asking here: 1) How can I make it work the way I need to? 2) How do I build a master file that links and updates a multitude of these files?

Master_Timesheet_Template.zipFetching info...

[Lewism]

I think I found a solution using the sumif function. Give me 10 minutes and I will have something.

*Edit*

I did it.

I slitted the two period in two columns. I have not checked the overtime tough.

Master_Timesheet_Template.zipFetching info...

Edited April 27, 2009 by Lewism

[ttam]

That is a very interesting approach. I'm pretty sure I can make it work with OT and everything else. Thanks a ton! I'll let you know if I get it working and post the full file in case anyone else needs to use the same kind of thing.

[Lewism]

The same thing could be done with the overtime since you will probably have the same problem. And later you can hide those columns.

As for the master file, Excel let you link files.

=[Test.xlsx]Sheet1!$B$3

Lets say you use this formula in "Master Template.xlsx", it will go pick the data of Cell B3 in Sheet1 in Test.xlsx.

To create a link easily, open the two files and type = and chose a cell on the other file.

However, you will have a do this for each file that you have in that directory. I believe it could be done with VBA to automate this.

[ttam]

Okay, I'm stuck again. I'm wondering if this is too complex for Excel and I need to use Access or something.

I'm wondering about the OT/Sick/Vaca time calculation and the primary Payroll Hours calculation. If you look at the formula for Payroll Hours, you will see some SUM calculations are off their proper ranges. The 14/15/16 day periods is moving the ranges improperly. It's as if we need a dynamic cell range to calculate based on an IF statement.

Oi this is complicated.

Here's an updated/modified version

Master_Timesheet_Template_2.zipFetching info...

[ttam]

I've added a column that places the word Begin and the word End in the row that begins and the row that ends the pay period. I've set the Payroll Hrs formula to something along these lines:

SUM(IF(D5="Begin",L5,IF(D6="Begin",L6,If(D7="Begin",L7,If(D8="Begin",L8,"")))):L21

It dynamically chooses the beginning of the range. It's kind of exactly what I was looking for, I'm just not sure it's perfect.

Master_Timesheet_Template_3.zipFetching info...

[ttam]

SOLVED! I'm pretty sure I got everything working. The long and short of the answer is that you can use IF statements to specify a range.

Ex: SUM(IF(C1=5,C1,IF(C2=5,C2)):C10)

I'm pretty happy with the outcome. I appreciate all your help guys.

Also, if anyone is looking for this kind of spreadsheet, go ahead and use it. I don't want to see anyone selling it though :)

Also, I added a formula that simply applied the words "This Pay Period," "Next Pay Period," or "Last Pay Period" for easy filtering. Filter that column and view only entries from this pay period. Woot!

Master_Timesheet_Template_Final.zipFetching info...

[ttam]

I now have a finished product. I have cleaned everything up and made it pretty and usable, as well as (more importantly) setting up the admin sheet that links to the individual files. I used VLOOKUP in that book to pull only the 4 fields (hours, OT, sick, vaca) based on a date that's in the Admin file. You'll obviously need to update the links in the Admin file to your filenames, as right now it comes up #N/A.

I'm uploading both files, unprotected, so you can learn from my 20 hour adventure.

Thanks to everyone for their help.

SemiMonthlyTimeSheet.zipFetching info...

[sikksnow]

  ttam said:

I now have a finished product. I have cleaned everything up and made it pretty and usable, as well as (more importantly) setting up the admin sheet that links to the individual files. I used VLOOKUP in that book to pull only the 4 fields (hours, OT, sick, vaca) based on a date that's in the Admin file. You'll obviously need to update the links in the Admin file to your filenames, as right now it comes up #N/A.

I'm uploading both files, unprotected, so you can learn from my 20 hour adventure.

Thanks to everyone for their help.

How do I adjust it for a pay schedule that ranges from the first of the month to the 15th, and 16th to the end of the month?