Time Calc

[Guest]

I have in one cell (say A1) time displayed as 16:45 (excel shows it as
11/6/2007 4:45:38 PM in the bar at the top). I am trying to create an if
statement in another cell to calculate if the time in cell A1 is between a
certain time frame (say 18:00-23:59) and to give me a 1 or a 0 based on the
calc. How can I convert the time in Cell A1 to a number without the date
showing?

=IF(AND(A1>=0.75,A1<1),1,0)


Any Help Greatly Appreciated.

Thanks

Robert B.

 

[Niek Otten]

Hi Robert,

=IF(AND(MOD(A1,1)>=0.75,MOD(A1,1)<1),1,0)

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|I have in one cell (say A1) time displayed as 16:45 (excel shows it as
| 11/6/2007 4:45:38 PM in the bar at the top). I am trying to create an if
| statement in another cell to calculate if the time in cell A1 is between a
| certain time frame (say 18:00-23:59) and to give me a 1 or a 0 based on the
| calc. How can I convert the time in Cell A1 to a number without the date
| showing?
|
| =IF(AND(A1>=0.75,A1<1),1,0)
|
|
| Any Help Greatly Appreciated.
|
| Thanks
|
| Robert B.

 

[Peo Sjoblom]

=IF(AND(MOD(A1,1)>=TIME(18,0,0),MOD(A1,1)<=TIME(23,59,59)),1,0)


I changed the 23:59 to 23:59:59, replace the last 59 with 0 is you really
wanted 1 minute to midnight
--


Regards,


Peo Sjoblom

 

[Guest]

Mr. Sjoblom,

Thanks so much - worked perfectly. I know that I would not have gotten it
without your help.

Robert B.

 

[Guest]

Mr. Otten,

Thanks so much - worked perfectly. I know that I would not have gotten it
without your help.

Robert B.

 

[Dana DeLouis]

=IF(AND(A1>=0.75,A1<1),1,0)

Would this work?

= --(HOUR(A1)>=18)