Formula check if in between time range from 2 data

Hi Guys,

How to check the provided timing was within 10:00 PM to 6:00 AM?

For example an employee key in the overtime From and To was 9:00 PM to 7:00 AM, is there a formula to auto show as "Yes/True" or "No/False"?

Thank you so much!!~

Attach example EXCEL file (not a picture, pasted text or any other than Excel format!). This file should be attached to a new post. Never change your original post (except admin's request).

In attached Excel file try not to use:

• merged cells
• password protection
• autorun VBA code
• unnecessary formatting like: colours, borders, aligning another than default, etc...
• unnecessary zooming/grouping/freezeing

You have words to logically describe the problem

To attach an Excel file to your post,

• desensitize data
• remeber that your example should reflect structure and type of data and contain the result what you want to achieve (manually created if necessary)
• click Go Advanced,
• scroll down until you see Manage Attachments,
• click that and select Browse,
• select your file and click Open,
• click Upload and you will see your attachment below Upload Files from a website
• click Close this window,
• click Submit reply

After that you should see attachment in your post

Ok here's the file.

I need to manual key in the highlighted part currently, is there a way to make a formula for auto calculation? T_T

Try these:

Cell E10 =MEDIAN($B$5,$C$5,C10+(B10>C10))-MEDIAN($B$5,$C$5,B10)+MEDIAN($B$5,$C$5,C10+(B10>C10)-1)-$B$5
Cell G10 =IF(E10<D10,"Yes","No")

Try in E10:

=IF(B10="","",IF(B10>C10,MAX(0,C10-$B$5)+MAX(0,$C$5-B10),MIN(C10,$C$5)-MAX(B10,$B$5)))

@ Phuocam:

Your solution stumbles on:
5:00 AM - 4:00 AM (returns 17:00 instead of 16:00)
4:00 AM - 4:00 AM (returns negative 2:00 instead of 0:00)

Use this formula
=IF(AND(B10<$B$5,C10<$B$5),0,IF(D10-1>$B$5,MIN($C$5,D10)-MAX($B$5,B10)+D10-1-$B$5,MIN($C$5,D10)-MAX($B$5,B10)))

How about this?
my head going to crack soon..

@Root_

You're right!