Originally Posted by
daddylonglegs
Try like this
=IF(A1="",0,IF((A3>=A1)+(A3<=A2)+(A2<A1)=2,1,0))
Thanks! I will give it a try.. I was just logging on to add this..
Okay.. so here it is..
A1 = Start B1= End C1 = Given Time
True
A1 11 PM
B1 7 AM
C1 11:45 PM
or
True
A1 1 PM
B1 10 AM
C1 7 PM
or
False
A1 11 AM
B1 7 PM
C1 8 PM
This is the first attempt that worked kind of like I wanted it to.
I will break it down as this is bit of a monster and there might be some people out there that can help me clean it up...
The $ are to lock the cell letter and cell number while copying and pasting.
This is to return a blank if there is nothing in the target cell:
=IF($A$1="","0",
This is to check to see if both times fall within the normal 24 hour period (not after midnight) and to see if the selected time falls between both times:
(IF(AND($A$1<$B$1,$A$1<=C1,C1<=$B$1),"1",
This is to check to see if the start time is "greater" than the end time.
(IF($A$1>$B$1,
If the the time seems to include midnight this will return a true value:
(IF(AND(($A$1+0.5))<=(C1+0.5),(C1)<=($B$1+1)),"1","0")),"0")))))
I am sure there is some stuff in here that isn't needed but as I said before it worked out to do what I was looking for.
Just a side note if you are looking to show this on a second day the times you are comparing against have to have a +1 to register.
That is it for now.. if you have any suggestions I would love to hear them..
Thanks,
:: Gritic ::
Bookmarks