I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?

Hi,

For basic hours
=MIN(SUM(A2:E2),38)

For ot hours
=MAX(SUM(A2:E2)-38,0)


Mike


"Keep It Simple Stupid" wrote:

I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?

Assuming your headers (Mon, Tue, etc) were in row 1, and the number of
hours in row 2, with everything starting in A1, this formula would do
what you want.

=IF(SUM(A2:E2)<=40,SUM(A2:E2)&" Hours","40 regular hrs,
"&(SUM(A2:E2)-40)& " Overtime Hrs")


--JP

On Oct 20, 10:49*am, Keep It Simple Stupid
wrote:
I need to keep my columns to a minimum. *

There are 5 columns for each employee (Monday-Friday). *Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon * * Tue * *Wed * *Thu * *Fri
8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. *Any ideas?

Assuming data is in A2:E2, use these array* formulae:

F2: =SUM(IF(A2:E2<=8,A2:E2,8))

for sum of regular hours, and:

G2: =SUM(IF(A2:E28,A2:E2-8))

for sum of overtime hours.

* Array formula need to be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual Enter. If you do this correctly
then Excel will wrap curly braces { } around the formula when viewed
in the formula bar - do not type these yourself. If you edit/amend the
formula you will need to use CSE again.

Hope this helps.

Pete


On Oct 20, 3:55*pm, Mike H wrote:
Hi,

For basic hours
=MIN(SUM(A2:E2),38)

For ot hours
=MAX(SUM(A2:E2)-38,0)

Mike

"Keep It Simple Stupid" wrote:



I need to keep my columns to a minimum. *

There are 5 columns for each employee (Monday-Friday). *Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon * * Tue * *Wed * *Thu * *Fri
8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. *Any ideas?- Hide quoted text -

- Show quoted text -

I'm not sure this is what I am looking for. I need the OT to be calculated
daily instead of weekly. The OT is based on 8 hour days. In my example, the
total should be 38 regular hours and 8 OT hours - whereas if it was based on
a weekly 40 hours it would have been 40 regular hours, 6 OT hours.

Basically, I need a way to sum everything over 8 hours, subtracting 8 hours
from each instance.

"Mike H" wrote:

Hi,

For basic hours
=MIN(SUM(A2:E2),38)

For ot hours
=MAX(SUM(A2:E2)-38,0)


Mike


"Keep It Simple Stupid" wrote:

I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?

To get the total OT

=SUM(IF(A1:A58,A1:A5-8))


To get the total regular time



=SUM(A1:A5)-SUM(IF(A1:A58,A1:A5-8))


both entered with ctrl + shift & enter



--


Regards,


Peo Sjoblom

"Keep It Simple Stupid" wrote
in message ...
I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?

Forgot about the CSE. Darn it! This is an incredible help! Thanks a bunch
everyone!

"Pete_UK" wrote:

Assuming data is in A2:E2, use these array* formulae:

F2: =SUM(IF(A2:E2<=8,A2:E2,8))

for sum of regular hours, and:

G2: =SUM(IF(A2:E28,A2:E2-8))

for sum of overtime hours.

* Array formula need to be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual Enter. If you do this correctly
then Excel will wrap curly braces { } around the formula when viewed
in the formula bar - do not type these yourself. If you edit/amend the
formula you will need to use CSE again.

Hope this helps.

Pete


On Oct 20, 3:55 pm, Mike H wrote:
Hi,

For basic hours
=MIN(SUM(A2:E2),38)

For ot hours
=MAX(SUM(A2:E2)-38,0)

Mike

"Keep It Simple Stupid" wrote:



I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?- Hide quoted text -

- Show quoted text -

For regular hours:
=SUM(A1:E1)-(SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8))

For overtime hours:
=SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8)

Just press ENTER


"Keep It Simple Stupid" wrote:

I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?

Hi,

As said for the total overtime in F1 =SUM(IF(A1:E18,A1:E1-8,0))
but for the total regular time in G1 =SUM(A1:E1)-F1

Adjust your ranges as necessary.

--
Thanks,
Shane Devenshire


"Keep It Simple Stupid" wrote:

I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?

You're welcome - glad to help.

Pete

On Oct 20, 4:19*pm, Keep It Simple Stupid
wrote:
Forgot about the CSE. *Darn it! *This is an incredible help! *Thanks a bunch
everyone!



"Pete_UK" wrote:
Assuming data is in A2:E2, use these array* formulae:

F2: * *=SUM(IF(A2:E2<=8,A2:E2,8))

for sum of regular hours, and:

G2: * *=SUM(IF(A2:E28,A2:E2-8))

for sum of overtime hours.

* Array formula need to be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual Enter. If you do this correctly
then Excel will wrap curly braces { } around the formula when viewed
in the formula bar - do not type these yourself. If you edit/amend the
formula you will need to use CSE again.

Hope this helps.

Pete

On Oct 20, 3:55 pm, Mike H wrote:
Hi,

For basic hours
=MIN(SUM(A2:E2),38)

For ot hours
=MAX(SUM(A2:E2)-38,0)

Mike

"Keep It Simple Stupid" wrote:

I need to keep my columns to a minimum. *

There are 5 columns for each employee (Monday-Friday). *Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon * * Tue * *Wed * *Thu * *Fri
8 * * * * *10 * * * 12 * * *6 * * * 10 * Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. *Any ideas?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

This one worked the best because the other ones were counting my 0's as
negative numbers, etc. (For overtime hours:
=SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8)
Thanks so much!


"Teethless mama" wrote:

For regular hours:
=SUM(A1:E1)-(SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8))

For overtime hours:
=SUMIF(A1:E1,"8")-(COUNTIF(A1:E1,"8")*8)

Just press ENTER


"Keep It Simple Stupid" wrote:

I need to keep my columns to a minimum.

There are 5 columns for each employee (Monday-Friday). Overtime is
calculated over 8 hours and I need to track how much total regular time &
over time for each employee for each month.

I want some kind of sum if forumula that will calculate everything over 8
and everything under 8.

Mon Tue Wed Thu Fri
8 10 12 6 10 Should be 38 regular hrs, 8
Overtime Hrs (employees will not always have at least 8 hours)

I've tried max/min formulas but not sure how I can do it with keeping my
columns/rows to a minmum. Any ideas?