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.
I think you meant column B, not A? (A is just dates)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.
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
Thank you FDibbins. Greatly appreciated.
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks