Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2005
    Posts
    27

    Unanswered: Doing a for each for a whole bunch of cells

    Hi,

    I have an Excel app which acts as a timesheet. I want to be able to loop thru several cells using a for each statement and see if their are any hours greater than 8 for a particular day and if so, accumulate hours in an OT entitlement field at the bottom of the sheet. Are there any ideas on how to do this?

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    You can do it with a formula or Visual Basic.
    See this post about doing an Array or Index formula to add values based on criteria:
    Vlookup on more than one column?

    Code:
    To start with I subtracted Start time from End Time and multiplied
    by 24 to get a number format from the the Start and End values formated
    as Time.
    
    If C3 is the End time and B3 the Start, this is the formula.
    =(C3-B3)*24
    
    Array Formula to Sum Column 'D':
    {=SUM((D2:D7>8)*D2:D7)}
    (Use Ctl+Shft+Enter to apply a formula as an Array Formula
     that will add the Curly Brackets and apply the array)
    
    A subtotal formula will total values in a filtered set of records.
    Use a subtotal with a custom filter to show values greater than 8.
    =SUBTOTAL(9,D2:D7)
    Attached Thumbnails Attached Thumbnails timecalc.gif  
    ~

    Bill

  3. #3
    Join Date
    Feb 2005
    Posts
    27

    Thanks

    Thanks a million Bill!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •