Re: Sum of Daily Overtime

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: 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: 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)
  • Time to and then from Midnight
    ... regular time and overtime also an overtime only option box that when selected ... places a 0 in the regular field and total hours become the overtime hours. ... The probelm at this stage is if the depart date and return date are the same ... Regular hours called [hrs within call] ...
    (microsoft.public.access.forms)
  • Re: How can I find the greatest possible sum within 12 months? A newbie...
    ... *one* sum, the maximum/highest sum, within a 12 month period. ... Roger Govier schrieb: ... You may have omitted to enter it as an array formula (see notes on ... Excel will insert them for you. ...
    (microsoft.public.excel.worksheet.functions)