Somewhat complex timesheet problem.
 

This is all based on a Monday to Friday bi-weekly timesheet.

The problem I am having is mostly employee related. A great majority of
them think that the €œPay Period Ending€� is the last day they work. If they
would have read their contracts, it is the Saturday at the end of the second
work week. (This is a college, so everything begins and ends on the weekend
even though nothing really starts or ends until the week. Stupid; I know.)

Anyway, I need a formula that will look at the last day an employee worked
for the pay period and then add the enough to the end so it will make the
€œPay Period Ending€� field display Saturday of the second week.

The following fields are on the timesheet.

A7: Date (mm/dd/yyyy) Formatted to display Dec 01

Work Hours
B7: Start Time
C7: End Time

Used Hours
D7: Sick
E7: Vacation
F7: Comp
G7: Personal
H7: Lunch
I7: Other

Total Hours
J7: Regular Worked =((C8-B8)*24)-I8
K7: Used Hours =SUM(D8:H8)
L7: Overtime =IF(M8-80,M8-8,0)
M7: Total Hours Paid =((C8-B8)*24)+M8-L8-I8

And in a merged cell above all of this is €œPay Period Ending€�. Label in A5
(merged with B5 and C5) and formula in D5 (merged all the way to G5).

Since this is for both part and full time employees, part-time has 12 spaces
for entry and full-time has 13 spaces. After that are Auto-Sums and other
crap.

Thanks.

Somewhat complex timesheet problem.
 

assuming your bi-weekly end dates are 9th December 2006, 23rd december and so
on try

=IF(COUNT(A7:A19),CEILING(MAX(A7:A19),14),"")

or if it's 2nd, 16th 30th December etc. try

=IF(COUNT(A7:A19),CEILING(MAX(A7:A19)-7,14)+7,"")

"Don" wrote:

This is all based on a Monday to Friday bi-weekly timesheet.

The problem I am having is mostly employee related. A great majority of
them think that the €œPay Period Ending€� is the last day they work. If they
would have read their contracts, it is the Saturday at the end of the second
work week. (This is a college, so everything begins and ends on the weekend
even though nothing really starts or ends until the week. Stupid; I know.)

Anyway, I need a formula that will look at the last day an employee worked
for the pay period and then add the enough to the end so it will make the
€œPay Period Ending€� field display Saturday of the second week.

The following fields are on the timesheet.

A7: Date (mm/dd/yyyy) Formatted to display Dec 01

Work Hours
B7: Start Time
C7: End Time

Used Hours
D7: Sick
E7: Vacation
F7: Comp
G7: Personal
H7: Lunch
I7: Other

Total Hours
J7: Regular Worked =((C8-B8)*24)-I8
K7: Used Hours =SUM(D8:H8)
L7: Overtime =IF(M8-80,M8-8,0)
M7: Total Hours Paid =((C8-B8)*24)+M8-L8-I8

And in a merged cell above all of this is €œPay Period Ending€�. Label in A5
(merged with B5 and C5) and formula in D5 (merged all the way to G5).

Since this is for both part and full time employees, part-time has 12 spaces
for entry and full-time has 13 spaces. After that are Auto-Sums and other
crap.

Thanks.

Somewhat complex timesheet problem.
 

Thank! I used the first one since the 23rd it the end of this pay period,
and it worked perfectly. Thanks again.

"daddylonglegs" wrote:

assuming your bi-weekly end dates are 9th December 2006, 23rd december and so
on try

=IF(COUNT(A7:A19),CEILING(MAX(A7:A19),14),"")

or if it's 2nd, 16th 30th December etc. try

=IF(COUNT(A7:A19),CEILING(MAX(A7:A19)-7,14)+7,"")

"Don" wrote:

This is all based on a Monday to Friday bi-weekly timesheet.

The problem I am having is mostly employee related. A great majority of
them think that the €œPay Period Ending€� is the last day they work. If they
would have read their contracts, it is the Saturday at the end of the second
work week. (This is a college, so everything begins and ends on the weekend
even though nothing really starts or ends until the week. Stupid; I know.)

Anyway, I need a formula that will look at the last day an employee worked
for the pay period and then add the enough to the end so it will make the
€œPay Period Ending€� field display Saturday of the second week.

The following fields are on the timesheet.

A7: Date (mm/dd/yyyy) Formatted to display Dec 01

Work Hours
B7: Start Time
C7: End Time

Used Hours
D7: Sick
E7: Vacation
F7: Comp
G7: Personal
H7: Lunch
I7: Other

Total Hours
J7: Regular Worked =((C8-B8)*24)-I8
K7: Used Hours =SUM(D8:H8)
L7: Overtime =IF(M8-80,M8-8,0)
M7: Total Hours Paid =((C8-B8)*24)+M8-L8-I8

And in a merged cell above all of this is €œPay Period Ending€�. Label in A5
(merged with B5 and C5) and formula in D5 (merged all the way to G5).

Since this is for both part and full time employees, part-time has 12 spaces
for entry and full-time has 13 spaces. After that are Auto-Sums and other
crap.

Thanks.