weekday (1 Viewer)

[deekras]

i have to figure out payroll. employees are supposed to work a certain amount of hours on a given day. Mon 9-5, Tues 10-1, etc.
if they worked more or less, i have to adjust the pay.

i have a table of the hours they are supposed to work each day
the table looks like this:

employee Mon-in Mon-out Tues-In Tues-Out .....

my timsheets look like this:
employee day1-in Day1-out Day2-in day2-out ....

the problem is that if an employee is here on monday, absent tuesday and here on wednesday then monday is day1 and wed is day2.

is there a way that i can link the 2 tables? or move the day into the correct columns so that the 2 tables match?

 

[dcx693]

Your table structure seems very inefficient. Why not structure it like this:
Employee
Day
InTime
Outtime

You don't then have to worry about empty days.

 

[deekras]

that is how i would do it. but i get the data that way.

 

[dcx693]

Ok, so are you saying that you need to move the data from the Monday, Tuesday, etc.. table into the Day1, Day2, ...etc table?

Why?

 

[deekras]

not really. i want to compare the time that the employee shuold have been at work to the time he actually was.

on mon, he shuold have been 9 hours, but was only 8 hours, etc.
on tues, he should have been 8 hours, was 10 hours.

 

[dcx693]

OK...What you're asking for is achievable in code, but why can't you then change the structure of your timesheets to be like the other table? That would be the simplest solution, from what I can see.

 

[deekras]

how?

i get the data that way from excel.

how do i say? if this column has "tues in" then move to column10
sometimes "tues in" is in column 5 sometimes column 7.

 

[Mile-O]

Restructure the spreadsheet on a hidden sheet using formulas to refer to the original positions.
Import the hidden sheet into your database.

 

[deekras]

what formulas would i use?