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

1. ## 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.

2. ## 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,"")

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

Thank you FDibbins. Greatly appreciated.

4. ## 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

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

#### 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