Unable to display Correct Text with Time reference

**sajeel** · 01-25-2011, 03:49 PM

Column B and C are opening and closing times of an exchange. For instance Product A exchange opens at 2300hrs and closes at 2140hrs the next day (or the exchange remains closed between 2140hrs and 2320hrs everday). Product B opens at 0710hrs and closes at 1620hrs. If the Time Now (Cell H1) is in between the opening and closing hours I would like to display that as "ON" in column D.

I have tried combinations of AND and if functions along with subtracting times but no joy. Any help would be appreciated. Thansk

**ChemistB** · 01-25-2011, 04:08 PM

Try this formula;
=IF(B2<C2,IF(AND(MOD($H$1,1)<C2,MOD($H$1,1)>B2),"ON","OFF"),IF(AND(MOD($H$1,1)>C2,MOD($H$1,1)<B2),"ON","OFF"))

MOD($H$1,1) is needed because NOW() contains both time and date and you need the date (whole numbers) stripped off. Does that work for you?

**6StringJazzer** · 01-25-2011, 04:11 PM

See attached. You have two problems.

The biggest problem is that NOW() returns a date + time, not just time. I replaced it with a formula that provides just the time portion.

The second problem is that you have intervals that start in one day and end the following day. That requires different arithmetic than when you start and stop the same day. See formulas that manage this.

**6StringJazzer** · 01-25-2011, 04:12 PM

I provided basically the same solution as ChemistB but I modified H1 instead of modifying the references to it.

Gotta remember to hit Preview before posting