Using a Custom Date in Cell

[Carl]

I am pulling in data from another application. The
Date/Time value comes in like so:

6/15/04 3:21 PM
6/15/04 3:21 PM
6/15/04 3:21 PM
6/15/04 3:21 PM
6/15/04 2:28 PM
6/15/04 2:03 PM
6/15/04 11:47 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:33 AM
6/15/04 9:32 AM
6/15/04 9:32 AM

I would like to place a formula that returns:

"Open" if the time is before 10:00 AM or
"Close" if the time is after 3:00 PM

Is it possible ?

Thank you in advance.

 

[Vasant Nanavati]

=IF(A1-INT(A1)<10/24,"Open",IF(A1-INT(A1)>15/24,"Close",""))

There's probably a much easier way but it escapes me at the moment.

 

[Andy B]

Hi

Try something like:
=IF(MOD(A2,1)<(1/24*10),"Open",IF(MOD(A2,1)>(1/24*15),"Close",""))

I've written it out like this so you can see where the reasoning comes from
(I hope!) The MOD bit leaves behind the fraction part of the value (which is
the time) and this is then compared with 10am (which is 1 day divided by 24
hours times 10 hours) and with 1500 (which is 1 day divided by 24 hours
times 15 hours).
I'm sure someone will better my solution - but it's a start!

 

[JE McGimpsey]

One way:

=IF(MOD(A1,1)<TIME(10,0,0),"Open",
IF(MOD(A1,1)>TIME(15,0,0),"Close","??")