Formula Check... Please

[Craig]

I didn't receive a response on my last post so I worked on my problem some and came up with some results. Could someone look over my formulas and tell me if I can achieve this same result any easier?

Here is a sample of the worksheet:
D E F G H I J K L M N O
22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total
23 off 12 11 11 11 11 off 40 15 1 10.00 $645.00
24


The code in column "K" is:
=SUM(IF(NOT(ISNUMBER(D23)),0,MIN(8,D23)),IF(NOT(ISNUMBER(E23)),0,MIN(8,E23)),IF(NOT(ISNUMBER(F23)),0,MIN(8,F23)),IF(NOT(ISNUMBER(G23)),0,MIN(8,G23)),IF(NOT(ISNUMBER(H23)),0,MIN(8,H23)),IF(NOT(ISNUMBER(I23)),0,MIN(8,I23)),IF(NOT(ISNUMBER(J23)),0,MIN(8,J23)))

The code in column "L" is:
=SUM(IF(AND(ISNUMBER(D23),D23>8),IF(D23>11,3,SUM(D23-8)),0),IF(AND(ISNUMBER(E23),E23>8),IF(E23>11,3,SUM(E23-8)),0),IF(AND(ISNUMBER(F23),F23>8),IF(F23>11,3,SUM(F23-8)),0),IF(AND(ISNUMBER(G23),G23>8),IF(G23>11,3,SUM(G23-8)),0),IF(AND(ISNUMBER(H23),H23>8),IF(H23>11,3,SUM(H23-8)),0),IF(AND(ISNUMBER(I23),I23>8),IF(I23>11,3,SUM(I23-8)),0),IF(AND(ISNUMBER(J23),J23>8),IF(J23>11,3,SUM(J23-8)),0))

The code in column "M" is:
=SUM(IF(AND(ISNUMBER(D23),D23>11),SUM(D23-11),0),IF(AND(ISNUMBER(E23),E23>11),SUM(E23-11),0),IF(AND(ISNUMBER(F23),F23>11),SUM(F23-11),0),IF(AND(ISNUMBER(G23),G23>11),SUM(G23-11),0),IF(AND(ISNUMBER(H23),H23>11),SUM(H23-11),0),IF(AND(ISNUMBER(I23),I23>11),SUM(I23-11),0),IF(AND(ISNUMBER(J23),J23>11),SUM(J23-11),0))

Can I get this result in a more simplified way?

Thanks Craig

 

[Leith Ross]

Hello Craig,

The easiest and most accurate method is to use User Defined Functions
(UDFs) to calculate straight time, overtime, and double time. I live
in California where the rules are a bit more complex than other states.
Recently, I wrote some UDFs for calculating California overtime. If you
want to email me a copy of your workbook, I could modify them for your
use. My emial is (e-mail address removed).

Sincerely,
Leith Ross

 

[Roger Govier]

Hi Craig

In K23
=SUMPRODUCT(--(ISNUMBER(D23:J23))*8)
in L23
=SUMPRODUCT(--(ISNUMBER(D23:J23)),(D23:J23)) - K23 -M23
IN M23
=SUMPRODUCT(--(ISNUMBER(D23:J23)),--(D23:J23>11))
--
Regards

Roger Govier


I didn't receive a response on my last post so I worked on my problem
some and came up with some results. Could someone look over my formulas
and tell me if I can achieve this same result any easier?

Here is a sample of the worksheet:
DEFGHIJKLMNO
22SunMonTueWedThuFriSatRegOT x1.5OTx 2RateTotal
23off1211111111off4015110.00$645.00
24


The code in column "K" is:
=SUM(IF(NOT(ISNUMBER(D23)),0,MIN(8,D23)),IF(NOT(ISNUMBER(E23)),0,MIN(8,E23)),IF(NOT(ISNUMBER(F23)),0,MIN(8,F23)),IF(NOT(ISNUMBER(G23)),0,MIN(8,G23)),IF(NOT(ISNUMBER(H23)),0,MIN(8,H23)),IF(NOT(ISNUMBER(I23)),0,MIN(8,I23)),IF(NOT(ISNUMBER(J23)),0,MIN(8,J23)))

The code in column "L" is:
=SUM(IF(AND(ISNUMBER(D23),D23>8),IF(D23>11,3,SUM(D23-8)),0),IF(AND(ISNUMBER(E23),E23>8),IF(E23>11,3,SUM(E23-8)),0),IF(AND(ISNUMBER(F23),F23>8),IF(F23>11,3,SUM(F23-8)),0),IF(AND(ISNUMBER(G23),G23>8),IF(G23>11,3,SUM(G23-8)),0),IF(AND(ISNUMBER(H23),H23>8),IF(H23>11,3,SUM(H23-8)),0),IF(AND(ISNUMBER(I23),I23>8),IF(I23>11,3,SUM(I23-8)),0),IF(AND(ISNUMBER(J23),J23>8),IF(J23>11,3,SUM(J23-8)),0))

The code in column "M" is:
=SUM(IF(AND(ISNUMBER(D23),D23>11),SUM(D23-11),0),IF(AND(ISNUMBER(E23),E23>11),SUM(E23-11),0),IF(AND(ISNUMBER(F23),F23>11),SUM(F23-11),0),IF(AND(ISNUMBER(G23),G23>11),SUM(G23-11),0),IF(AND(ISNUMBER(H23),H23>11),SUM(H23-11),0),IF(AND(ISNUMBER(I23),I23>11),SUM(I23-11),0),IF(AND(ISNUMBER(J23),J23>11),SUM(J23-11),0))

Can I get this result in a more simplified way?

Thanks Craig

 

[Bob Phillips]

K23: =SUM(IF(ISNUMBER(D23:J23)*(D23:J23>8),8,D23:J23))
L23: =SUM(IF(ISNUMBER(D23:J23),IF(D23:J23>11,3,D23:J23-8)))
M23: =SUM(D23:J23)-SUM(K23:L23)

the first two are array formulae, so commitwith Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


I didn't receive a response on my last post so I worked on my problem some and came up with some results. Could someone look over my formulas and tell me if I can achieve this same result any easier?

Here is a sample of the worksheet:
D E F G H I J K L M N O
22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total
23 off 12 11 11 11 11 off 40 15 1 10.00 $645.00
24


The code in column "K" is:
=SUM(IF(NOT(ISNUMBER(D23)),0,MIN(8,D23)),IF(NOT(ISNUMBER(E23)),0,MIN(8,E23)),IF(NOT(ISNUMBER(F23)),0,MIN(8,F23)),IF(NOT(ISNUMBER(G23)),0,MIN(8,G23)),IF(NOT(ISNUMBER(H23)),0,MIN(8,H23)),IF(NOT(ISNUMBER(I23)),0,MIN(8,I23)),IF(NOT(ISNUMBER(J23)),0,MIN(8,J23)))

The code in column "L" is:
=SUM(IF(AND(ISNUMBER(D23),D23>8),IF(D23>11,3,SUM(D23-8)),0),IF(AND(ISNUMBER(E23),E23>8),IF(E23>11,3,SUM(E23-8)),0),IF(AND(ISNUMBER(F23),F23>8),IF(F23>11,3,SUM(F23-8)),0),IF(AND(ISNUMBER(G23),G23>8),IF(G23>11,3,SUM(G23-8)),0),IF(AND(ISNUMBER(H23),H23>8),IF(H23>11,3,SUM(H23-8)),0),IF(AND(ISNUMBER(I23),I23>8),IF(I23>11,3,SUM(I23-8)),0),IF(AND(ISNUMBER(J23),J23>8),IF(J23>11,3,SUM(J23-8)),0))

The code in column "M" is:
=SUM(IF(AND(ISNUMBER(D23),D23>11),SUM(D23-11),0),IF(AND(ISNUMBER(E23),E23>11),SUM(E23-11),0),IF(AND(ISNUMBER(F23),F23>11),SUM(F23-11),0),IF(AND(ISNUMBER(G23),G23>11),SUM(G23-11),0),IF(AND(ISNUMBER(H23),H23>11),SUM(H23-11),0),IF(AND(ISNUMBER(I23),I23>11),SUM(I23-11),0),IF(AND(ISNUMBER(J23),J23>11),SUM(J23-11),0))

Can I get this result in a more simplified way?

Thanks Craig

 

[Bob Phillips]

An update

K23: =SUM(IF(ISNUMBER(D23:J23)*(D23:J23>8),8,D23:J23))
L23: =SUM(IF(ISNUMBER(D23:J23),IF(D23:J23<8,0,IF(D23:J23>11,3,D23:J23-8))))
M23: =SUM(D23:J23)-SUM(K23:L23)

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Roger Govier]

Craig

Forget my posting it is rubbish.
Realised just after switching off machine, and I have been off line for
a while.

 

[Craig]

Thanks... that did the trick. Much more condensed!
Craig
K23: =SUM(IF(ISNUMBER(D23:J23)*(D23:J23>8),8,D23:J23))
L23: =SUM(IF(ISNUMBER(D23:J23),IF(D23:J23>11,3,D23:J23-8)))
M23: =SUM(D23:J23)-SUM(K23:L23)

the first two are array formulae, so commitwith Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


I didn't receive a response on my last post so I worked on my problem some and came up with some results. Could someone look over my formulas and tell me if I can achieve this same result any easier?

Here is a sample of the worksheet:
D E F G H I J K L M N O
22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total
23 off 12 11 11 11 11 off 40 15 1 10.00 $645.00
24


The code in column "K" is:
=SUM(IF(NOT(ISNUMBER(D23)),0,MIN(8,D23)),IF(NOT(ISNUMBER(E23)),0,MIN(8,E23)),IF(NOT(ISNUMBER(F23)),0,MIN(8,F23)),IF(NOT(ISNUMBER(G23)),0,MIN(8,G23)),IF(NOT(ISNUMBER(H23)),0,MIN(8,H23)),IF(NOT(ISNUMBER(I23)),0,MIN(8,I23)),IF(NOT(ISNUMBER(J23)),0,MIN(8,J23)))

The code in column "L" is:
=SUM(IF(AND(ISNUMBER(D23),D23>8),IF(D23>11,3,SUM(D23-8)),0),IF(AND(ISNUMBER(E23),E23>8),IF(E23>11,3,SUM(E23-8)),0),IF(AND(ISNUMBER(F23),F23>8),IF(F23>11,3,SUM(F23-8)),0),IF(AND(ISNUMBER(G23),G23>8),IF(G23>11,3,SUM(G23-8)),0),IF(AND(ISNUMBER(H23),H23>8),IF(H23>11,3,SUM(H23-8)),0),IF(AND(ISNUMBER(I23),I23>8),IF(I23>11,3,SUM(I23-8)),0),IF(AND(ISNUMBER(J23),J23>8),IF(J23>11,3,SUM(J23-8)),0))

The code in column "M" is:
=SUM(IF(AND(ISNUMBER(D23),D23>11),SUM(D23-11),0),IF(AND(ISNUMBER(E23),E23>11),SUM(E23-11),0),IF(AND(ISNUMBER(F23),F23>11),SUM(F23-11),0),IF(AND(ISNUMBER(G23),G23>11),SUM(G23-11),0),IF(AND(ISNUMBER(H23),H23>11),SUM(H23-11),0),IF(AND(ISNUMBER(I23),I23>11),SUM(I23-11),0),IF(AND(ISNUMBER(J23),J23>11),SUM(J23-11),0))

Can I get this result in a more simplified way?

Thanks Craig

 

[Craig]

Bob... these formulas worked fine as long as the person worked 8 hours or more or didn't work in a particular day.
If the person worked less than 8 hour in a day... OT in the week it doesn't calculate correctly. In turn then it throws out the OT2 Calculation.
As an example if the person worked Monday: 12hrs Tuesday: 7hrs the OT calculation shows 2hrs instead of the actual 3hrs. If they only
worked 4hrs on Tuesday the OT calculation shows -1hrs.

Can this be fixed?

Craig

K23: =SUM(IF(ISNUMBER(D23:J23)*(D23:J23>8),8,D23:J23))
L23: =SUM(IF(ISNUMBER(D23:J23),IF(D23:J23>11,3,D23:J23-8)))
M23: =SUM(D23:J23)-SUM(K23:L23)

the first two are array formulae, so commitwith Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)


I didn't receive a response on my last post so I worked on my problem some and came up with some results. Could someone look over my formulas and tell me if I can achieve this same result any easier?

Here is a sample of the worksheet:
D E F G H I J K L M N O
22 Sun Mon Tue Wed Thu Fri Sat Reg OT x1.5 OTx 2 Rate Total
23 off 12 11 11 11 11 off 40 15 1 10.00 $645.00
24


The code in column "K" is:
=SUM(IF(NOT(ISNUMBER(D23)),0,MIN(8,D23)),IF(NOT(ISNUMBER(E23)),0,MIN(8,E23)),IF(NOT(ISNUMBER(F23)),0,MIN(8,F23)),IF(NOT(ISNUMBER(G23)),0,MIN(8,G23)),IF(NOT(ISNUMBER(H23)),0,MIN(8,H23)),IF(NOT(ISNUMBER(I23)),0,MIN(8,I23)),IF(NOT(ISNUMBER(J23)),0,MIN(8,J23)))

The code in column "L" is:
=SUM(IF(AND(ISNUMBER(D23),D23>8),IF(D23>11,3,SUM(D23-8)),0),IF(AND(ISNUMBER(E23),E23>8),IF(E23>11,3,SUM(E23-8)),0),IF(AND(ISNUMBER(F23),F23>8),IF(F23>11,3,SUM(F23-8)),0),IF(AND(ISNUMBER(G23),G23>8),IF(G23>11,3,SUM(G23-8)),0),IF(AND(ISNUMBER(H23),H23>8),IF(H23>11,3,SUM(H23-8)),0),IF(AND(ISNUMBER(I23),I23>8),IF(I23>11,3,SUM(I23-8)),0),IF(AND(ISNUMBER(J23),J23>8),IF(J23>11,3,SUM(J23-8)),0))

The code in column "M" is:
=SUM(IF(AND(ISNUMBER(D23),D23>11),SUM(D23-11),0),IF(AND(ISNUMBER(E23),E23>11),SUM(E23-11),0),IF(AND(ISNUMBER(F23),F23>11),SUM(F23-11),0),IF(AND(ISNUMBER(G23),G23>11),SUM(G23-11),0),IF(AND(ISNUMBER(H23),H23>11),SUM(H23-11),0),IF(AND(ISNUMBER(I23),I23>11),SUM(I23-11),0),IF(AND(ISNUMBER(J23),J23>11),SUM(J23-11),0))

Can I get this result in a more simplified way?

Thanks Craig