Dale Bennett
Board Regular
- Joined
- Oct 10, 2004
- Messages
- 168
In a current worksheet I use Jwalk (similar to Data/Form) to manually Input/append Start and Stop times for my employees. They are house cleaners and clock in and out 2,3,4,5 or 6 six times a day.
To calculate payroll we enter these actual times into an excel sheet – the one attached. There is additional information that gets input (ie. Client codes, how many cleaners worked together for the day and who drove)
These inputs create rows of data – each row representing a full work week for one cleaner. This process is working properly.
I need to be able to tie this data together with income record keeping creating a ckeck and balance system for work complete vs income.
I need to develop another ever-appending database/worksheet that keeps track of corresponding expected income. In other words, if a house was cleaned (indicated by the exisitance of IN & OUT times recorded in the payroll database) a payment for the cleaning in antiscipated. I need a formula or set of formulas that creates a row in a 2nd worksheet for each house that was cleaned that shows that a payment check is expected. Using tables of data that are already in place and functional, I need to lookup and drop into the income sheet such things as client code, client name, estabished cleaning fee, ect.
THEN, when the check is in hand we will simple manually input the check amount, check date and check number into the record/row that had previously been created. This method will maintain proper records as well as provide a visual confirmation that the check arrived and was for the correct amount.
The filelds / cells for the income database (yet to be created) would be:
1. Client code
2. Client first name
3. Client last name
4. Cost
5. Check date
6. Check amount
7. Check number
1, 2, 3, & 4 will be looked up from “Master Client Database”
5, 6 & 7 will be manually entered
The created record/rows will result from your formula looking at the IN & OUT times and related data from the “Payroll Hours Input” worksheet. The new worksheet where the cleanings and income match up will be called, let’s say, “Completed Cleanings & Collected Checks”.
The nut to crack here is creating an ever-appending database in a worksheet that will allow for subsequent related data entry.
By the way, the attached worksheet contains only 26 of the 89 colums – limited by the html maker. But the other colums, for the most part are merely more days of the week.
You will need actual column numbers from the lookup databases before all is said and done. The master client database is 255 columns wide, however only a few (client code, name cost are pertinent.
Thanks to whoever takes this project on.
Dale
To calculate payroll we enter these actual times into an excel sheet – the one attached. There is additional information that gets input (ie. Client codes, how many cleaners worked together for the day and who drove)
These inputs create rows of data – each row representing a full work week for one cleaner. This process is working properly.
| C2C Payroll Hours Input V1.3 12-01-04.xls | ||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
| 1 | MOSub-Cons | Didtheydrive? | MO1stCC | MO1stIN | MO1stOUT | MO2ndCC | MO2ndIN | MO2ndOUT | TUSub-Cons | Didtheydrive? | TU1stCC | TU1stIN | TU1stOUT | TU2ndCC | TU2ndIN | TU2ndOUT | WESub-Cons | Didtheydrive? | WE1stCC | WE1stIN | WE1stOUT | WE2ndCC | WE2ndIN | WE2ndOUT | ||
| 2 | 2 | y | 12 | 11:11AM | 4:00PM | 13 | 12:12 | 17:12 | 2 | y | 12 | 11:11AM | 4:00PM | 13 | 12:12 | 17:12 | 2 | y | 12 | 11:11AM | 4:00PM | 13 | 12:12 | 17:12 | ||
| 3 | 1 | Y | 658 | 9:14AM | 12:07PM | 1 | Y | 930 | 9:19AM | 1:15PM | 1 | Y | 1007 | 9:19AM | 1:42PM | |||||||||||
Input Hours | ||||||||||||||||||||||||||
I need to be able to tie this data together with income record keeping creating a ckeck and balance system for work complete vs income.
I need to develop another ever-appending database/worksheet that keeps track of corresponding expected income. In other words, if a house was cleaned (indicated by the exisitance of IN & OUT times recorded in the payroll database) a payment for the cleaning in antiscipated. I need a formula or set of formulas that creates a row in a 2nd worksheet for each house that was cleaned that shows that a payment check is expected. Using tables of data that are already in place and functional, I need to lookup and drop into the income sheet such things as client code, client name, estabished cleaning fee, ect.
THEN, when the check is in hand we will simple manually input the check amount, check date and check number into the record/row that had previously been created. This method will maintain proper records as well as provide a visual confirmation that the check arrived and was for the correct amount.
The filelds / cells for the income database (yet to be created) would be:
1. Client code
2. Client first name
3. Client last name
4. Cost
5. Check date
6. Check amount
7. Check number
1, 2, 3, & 4 will be looked up from “Master Client Database”
5, 6 & 7 will be manually entered
The created record/rows will result from your formula looking at the IN & OUT times and related data from the “Payroll Hours Input” worksheet. The new worksheet where the cleanings and income match up will be called, let’s say, “Completed Cleanings & Collected Checks”.
The nut to crack here is creating an ever-appending database in a worksheet that will allow for subsequent related data entry.
By the way, the attached worksheet contains only 26 of the 89 colums – limited by the html maker. But the other colums, for the most part are merely more days of the week.
You will need actual column numbers from the lookup databases before all is said and done. The master client database is 255 columns wide, however only a few (client code, name cost are pertinent.
Thanks to whoever takes this project on.
Dale
