Time Calc
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. |
Time Calc
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. |
Time Calc
=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. |
Time Calc
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. |
Time Calc
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. |
Time Calc
=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. |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com