If Statement for Time Range Help

[Realjoshtodd]

I'm needing help with as time range IF statement I'm working on.

I have in Row 1 (starting in Column D) time listed out in 15 minute breaks starting at 6 am. It is in military time currently. (06:00, 06:15, 06:30, 06:45, 07:00.... 23:45, 00:00, 00:15....)


In Column B2 I have the start time of an event (06:00)
In Column C2 I have the end time of an event (16:00)

I want each block in that row to say "Yes" if the time in Row 1 inside the event time. I want it to be "" if its not inside the event time.

I have tested out a couple different formulas but keeping running into a problem when the event time runs from lets say 21:00 to 06:00. Because of the change over in time it doesn't formulate correctly.

Here are the ones I've tried.

=IF(AND(D1>$B2,D1<$C2),"Yes","No")
=IF(AND(D1>=MIN($B$2:$C$2),D1<=MAX($B$2:$C$2)),"YES","")
=IF(AND($C3>D1,$B3<D1),"Yes","NO")

Some work for times before 00:00 (midnight) but none for all of the times.

Any help would be great.

 

[Joe4]

It is important to understand how Excel stores dates and times.
It actually stores them as numbers, specifically then number of days since 1/0/1900 (to see this, enter any date in a cell and change the format to "General" and see what it looks like).
So "1" represents one day. And a fraction represent the time component. So 6:00 AM is equal to 0.25 (6/24).

The second part to understand is that any valid time entry really has a date component associated with it (regardless of whether or not you choose to show the date piece in the format or not). So as you pass midnight, the day will be incremented to the next day (even if you are not showing it).

So, if we want to compare times only, and don't care about the date piece, then we want to strip off the whole numbers (days from the formula). Here is a method that show how to only return the decimal portion of a number:
https://exceljet.net/formula/get-decimal-part-of-a-number

If we apply that to your first formula, we get this:

=IF(AND((D1-TRUNC(D1))>=($B$2-TRUNC($B$2)),(D1-TRUNC(D1))<=($C$2-TRUNC($C$2))),"Yes","No")

See if that does what you want.

If it does not, please provide us with an actual example that does not work.

 

[Realjoshtodd]

Ok so I used this formula


=IF(AND((F1-TRUNC(F1))>=($C$2-TRUNC($C$2)),(F1-TRUNC(F1))<=($E$2-TRUNC($E$2))),"Yes","No")

F1 = the Time 6:00

C2 = Event Start Time

E2 = Event End Time


It worked for all of the times before midnight (00:00) anything that should have said Yes after that (Event start and end is 21:00 to 06:00) only shows as NO in the Number 2 row for the times that should say yes.

It is important to understand how Excel stores dates and times.
It actually stores them as numbers, specifically then number of days since 1/0/1900 (to see this, enter any date in a cell and change the format to "General" and see what it looks like).
So "1" represents one day. And a fraction represent the time component. So 6:00 AM is equal to 0.25 (6/24).

The second part to understand is that any valid time entry really has a date component associated with it (regardless of whether or not you choose to show the date piece in the format or not). So as you pass midnight, the day will be incremented to the next day (even if you are not showing it).

So, if we want to compare times only, and don't care about the date piece, then we want to strip off the whole numbers (days from the formula). Here is a method that show how to only return the decimal portion of a number:
https://exceljet.net/formula/get-decimal-part-of-a-number

If we apply that to your first formula, we get this:

=IF(AND((D1-TRUNC(D1))>=($B$2-TRUNC($B$2)),(D1-TRUNC(D1))<=($C$2-TRUNC($C$2))),"Yes","No")

See if that does what you want.

If it does not, please provide us with an actual example that does not work.

 

[Joe4]

OK, I think I see now. I was assuming that the End Time is always after the Start Time.
But if you have a situation that spans two days, and Start Time is before End Time (i.e. Start Time 21:00 and End Time 6:00 AM), that won't work.

It seems to me that since you are spanning two days, dates DO need to come into play here. Otherwise, you do not know which day to apply a particular time to (Day 1 or Day 2).