excell - payroll carry over

  • Thread starter Thread starter bob engleman
  • Start date Start date
B

bob engleman

Hi

This has to do with carry over of time on a payroll
sheet. Time may go in on a wednesday but, overtime
continues to add up thru saturday.

a b c d e f g h i j k l m n
1
2
3

=IF(NOT((SUM(last!h1:m1)>0)),(SUM(last!a1:f1)))
+SUM(last!h1:m1)

Problem. I need this equation to also take into

consideration, if column n > 0 no calculation need be
done, and when cells h:m = 0 a calculation must be done
if column g =0

Please help
 
Hi Biff

This is a payroll book
each sheet represents a 15 day period
but it is possible to have a 16 day period
(4) 7 day weeks must fit on each sheet (columns)
rows are names of employees
cells become hours worked per day
the second column represents hours carried over from last
week of previous payroll for overtime calculation
overtime is calculated if 40 hours are exceeded Sun. thru
Sat.
This is my carryover formula

=IF(NOT((SUM(last!X1:AC1)>0)),(SUM(last!Q1:V1)))+SUM
(last!X1:AC1)

the last week of this sheet can only ever have a Sun.
entry
formula works

the next to last week is the problem
if there are entries in the first six cells
formula works
what this formula must take into account
if column (w) has a number > than 0,
there would be no carry over
how do I state this and how do I imbed this in first part
of formula

THIS WOULD BE NO CARRY OVER PAYROLL ENDS ON SAT.
Q R S T U V W X Y Z AA AB AC AD
sun mon tue wed thu fri sat sun mon tue wed thu fri sat
1 8 8 8 8 8 8

MY FORMULA WOULD GIVE ME 40 HOURS CARRY OVER
2 8 8 8 8 8

I am almost getting confused trying to give a demonstration

I believe I need to imbed some statement in the first part
of my formula that will take into account a positive entry
in column w
 
Hi Bob,

It's kind of difficult trying to follow your sample data
but this is what I think you're trying to accomplish.
=IF(NOT((SUM(last!X1:AC1)>0)),(SUM(last!Q1:V1)))+SUM
(last!X1:AC1)
Click to expand...

In your formula the NOT function is not needed. There is
not an argument if the condition SUM(last!X1:AC1)>0 is
FALSE, so it defaults to FALSE(or in this case a returned
value of zero).

If I understand your explanation, try this formula:

=IF(W2>0,SUM(X2:AC2),SUM(Q2:V2,X2:AC2))

If I'm completely off the mark and if it's possible, you
can email a copy of the file so that I may better
understand your needs. What you want to do is actually
pretty simple. However, me trying to picture in my mind
the structure of the spreadsheet leaves alot of room for
error. I also understand that payroll info is very
sensitive but I'd be glad to help. You would need to
change my address to biff_in_pitt

Biff
 
Back
Top