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

[chriswhite1982]

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.

[FDibbins]

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,"")

[chriswhite1982]

Thank you FDibbins. Greatly appreciated.

[FDibbins]

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