+ Reply to Thread
Results 1 to 4 of 4

Cell Formula to count time with "Greater Than or Equal to" and "Less Than"

  1. #1
    Registered User
    Join Date
    05-25-2013
    Location
    Yonkers NY
    MS-Off Ver
    Excel 2010
    Posts
    28

    Cell Formula to count time with "Greater Than or Equal to" and "Less Than"

    This formula is to count Night Rate Overtime. I am having a hard time doing a SUMSIF formula with 3 conditions. I have enclosed a sample worksheet. My problem is with Greater Than or Equal to in relation to 12pm and Less than 4am. Any advise would be greatly appreciated. Thank you.
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Cell Formula to count time with "Greater Than or Equal to" and "Less Than"

    1. Under Col A, Employee starts on or after 12pm.
    2. Under Col A, Employee starts before 4am. (So on or afer 12 noon but before 4am
    3. Under Col G, the compensation is "Cash"
    So only cells H4 and H7 should have anything in them.
    I think you meant column B, not A? (A is just dates)

    In B4 you have 12:00 pm - that is 12 noon, not 12 am, so that will disqualify that row
    In B7 you have 10:00:00 PM - that is way after 4am, so that is disqualified as well

    This is a formula that should give you what you want...
    =IF(AND(B2>=0,B2<4/24,G2="cash"),E2,"")
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    05-25-2013
    Location
    Yonkers NY
    MS-Off Ver
    Excel 2010
    Posts
    28

    Re: Cell Formula to count time with "Greater Than or Equal to" and "Less Than"

    Thank you FDibbins. Greatly appreciated.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Cell Formula to count time with "Greater Than or Equal to" and "Less Than"

    Happy to help and thanks for the feedback

    Remember that as far as excel is concerned, it treats time as a fraction/decimal of 1, so 12 noon, although it looks like 12:00 os actually 0.5

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1