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.

Hi Robert,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Robert B." wrote in message ...
|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.

=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


"Robert B." wrote in message
...
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.

Mr. Sjoblom,

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

Robert B.

"Peo Sjoblom" wrote:

=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


"Robert B." wrote in message
...
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.

Mr. Otten,

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

Robert B.

"Niek Otten" wrote:

Hi Robert,

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Robert B." wrote in message ...
|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.

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

Would this work?

= --(HOUR(A1)=18)

--
Dana DeLouis


"Robert B." wrote in message
...
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.