Formula for: Does a time fall bewteen two cells (24 hour clock)

[Crablouse]

Hey all,

I am in need of some help:

I have a cell for Arrival Time (A1) and a Cell for Depart Time (B1).
I also have a cell that has a specific time such as 1:00 or 2:00, etc (C1).

I am in need of a formula that will tell me if C1 falls within A1 and B1.

This is a 24 hour business so some times will cross midnight such as Arrival time of 23:00 and Depart time of 2:00

Is this doable?

[huuthang_bd]

Hi

How to know time on C1 belong to which day? For example, Arrival time of 13:00 (today), Depart time of 2:00 (tomorrow) and C1 is 14:00. So, how to know C1 is today or tomorrow?

[Crablouse]

I would want to know if 1:00 or 2:00, etc falls between two different times. The arrival and depart COULD be on different days but not necessarily

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[bebo021999]

I would want to know if 1:00 or 2:00, etc falls between two different times. The arrival and depart COULD be on different days but not necessarily

OK, Let try:

=IF(A1<B1,IF(OR(C1<=A1,C1>=B1),"Yes","No"),IF(AND(C1<=A1,C1>=B2),"Yes","No"))

[Crablouse]

OK, Let try:

=IF(A1<B1,IF(OR(C1<=A1,C1>=B1),"Yes","No"),IF(AND(C1<=A1,C1>=B2),"Yes","No"))

Hey Bebo,

I couldn't get that to work for me.

I have attached a cleaned up version of what I am trying to do

Column A: When the customer arrives (Date and Time)
Column B: When the customer arrives (just Time)
Column C: When the customer leaves (Date and Time)
Column D: When the customer leaves (just Time)

Column E: Result of - was the customer in the department at 1:00 am?
Column F: Result of - was the customer in the department at 2:00 am?
Column F: Result of - was the customer in the department at 3:00 am?
etc
etc

[MrShorty]

Since columns A and C contain both date and time information, it would be a simple "between" function, if I had a way to get date and time information from row 1. Here's what I did.

1) Helper columns to get date+time for comparison. In J2, I enter =INT($A2)+TIME(1,0,0) [Note that I am assuming that, because all your other times are 24 hour clock, I am assuming your 1:00 to 5:00 are AM]. (The table automatically fills to the bottom and converts to structured table references). Copy across, replacing TIME(1,0,0) with TIME(2,0,0), TIME(3,0,0), etc.
2) Now my formula for column E is =AND($A2<=J2,J2<=$C2). Copy into columns F:I. This gives me a TRUE when the time is between and FALSE when not.

I'm sure that someone could nest the two functions together, but I did not bother at this point. Will something like that work for you?