Check if the in and out time falls between a specific time interval

srivibish

New Member
Joined
Feb 20, 2017
Messages
4
I am looking for a solution after a long trial and error workings. I have 2 columns in and out with date and time. I want to check if the in and out falls between the interval 10PM to 8AM of next day. Further, for those which falls within this range, I want to check atleast minimum of 3 hours are falling within this interval. My data looks like below in col A and B.
I need the output in column C and D where column C gives me the in and out time falls between the interval and Col D give me if it falls more than 3 hours between 10PM to 8AM.

InOutBetween 10-8<=3hrs
09/24/2019 06:00:1809/25/2019 01:00:22YesYes
09/09/2019 17:54:5809/10/2019 03:32:03YesYes
09/03/2019 10:19:4909/03/2019 23:22:40YesNo
10/01/2019 00:01:2210/01/2019 11:49:39YesYes
09/24/2019 23:59:4609/25/2019 09:10:38YesYes
09/09/2019 13:11:4109/10/2019 01:36:47YesYes
09/09/2019 13:11:4109/09/2019 21:36:47NoNo
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!


Cross posted at: Check if the in and out time falls between a specific time interval
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

HighAndWilder

Board Regular
Joined
Nov 4, 2006
Messages
121
For an example - how does 10/01/2019 00:01:22 to 10/01/2019 11:49:39 (Data row 4) fall within 10PM and 8AM of the next day?
 

srivibish

New Member
Joined
Feb 20, 2017
Messages
4
The entire IN and Out need not to be falls within the interval. If you see the row 4 data, the in time is early morning 1AM and out time is 8AM which falls between the 10PM to 8AM interval. Hence, I have captured as Yes for that
 

srivibish

New Member
Joined
Feb 20, 2017
Messages
4
Sorry, the out is 11.49. My Miss.

"The entire IN and Out need not to be falls within the interval. If you see the row 4 data, the in time is early morning 1AM and out time is around 11AM which falls between the 10PM to 8AM interval. Hence, I have captured as Yes for that

10/01/2019 00:01:2210/01/2019 11:49:39YesYes
Are you referring to the same set of data as you have posted?
This is what I am referring
 

Forum statistics

Threads
1,089,217
Messages
5,406,916
Members
403,111
Latest member
Donbozone

This Week's Hot Topics

Top