Expert: Tom Ogilvy - 8/28/2008

Question
QUESTION: I am trying to use the following statement,but I am having problems with times that cross the midnight boundary.

IF($D10>$E10,IF(AND($D10<=(BZ$9+1),($E10)>BZ$9),"X",""),IF(AND($D10<=BZ$9,$E10>BZ$9),"X",""))

Col D10 is the start time
Col E10 is the finish time
Col bz is the hour ie 00:00
Col bz10 is where the formula is



ANSWER: REG,
Try this:
=if(mod(BZ9-D10,1)<Mod(E10-D10,1),"X","")

this is applicable to elapsed periods less than 24 hours (but can go over midnight)

apply $ signs appropriate to your needs/layout.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: worked really well.however,i have just noticed it doesn't put an x in the start time.

Answer
I am not sure what is going on here.  I thought I just answered this, but

I am not sure what you mean by " it doesn't put an x in the start time."

If I make BZ9 match the start time, I get an X in BZ10.  If BZ9 is calculated, there could be slight differences between the start time and BZ9 and yet they display the same.   If you format each cell as General, you can see the underlying time serial number.  Otherwise, explain what you mean.  

--
Regards,
Tom Ogilvy