Re: Sum of Daily Overtime

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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:E2>8,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 <Mi...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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 -

.

Relevant Pages

  • Re: Sum of Daily Overtime
    ... for sum of overtime hours. ... Shift-Enter (CSE) instead of the usual Enter. ... calculated over 8 hours and I need to track how much total regular time & ... Overtime Hrs ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Complex conditional summing - array COUNT works, array SUM gives #VALUE
    ... The data/time column A is much as you'd guessed, text strings, and the ... goal is to sum all the hourly values for an entire day after adjusting ... if I change the array formula to ... C2:E20 = random numbers including empty cells, ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Setting Saturday and Sunday as Overtime
    ... any work performed outside those hours is overtime work. ... But Project doesn't ever automatically schedule work during those OT hours - if you just enter a task with a duration of 10 days and assign a resource to it, the work will ONLY be scheduled to occur during regular working hours and any hours the calendar would consider OT hours are skipped over. ...
    (microsoft.public.project)
  • Re: Excel Question - Last Twelve Month Calculation
    ... If the data values you want to sum are in row 2, starting in column A, ... This will find the right-most used cell in row 2, ... This is an array formula, so you must press CTRL SHIFT ENTER ... the formula enclosed in curly braces. ...
    (microsoft.public.excel.worksheet.functions)
  • Re: Sum of calculated fields
    ... Since this seems to be in a report, ... property instead of using Sum. ... total overtime add a text box named txtRunOvertime with the ... Note that your calculation of midnight is overly complex. ...
    (microsoft.public.access.formscoding)