Time Between?

StrangeLuck

New Member
Joined
Mar 25, 2002
Messages
15
Is there a way to use a formula like :

If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

to determine if a given cell occurs between the two listed times? Thanks!
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
On 2002-04-22 14:16, StrangeLuck wrote:
Is there a way to use a formula like :

If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

to determine if a given cell occurs between the two listed times? Thanks!

formula wise it simply be if(a1<.25

for a cell formatted as time.

if it is time and date it would be
if(MOD(A1,1)<.25,...
 
Upvote 0
On 2002-04-22 14:16, StrangeLuck wrote:
Is there a way to use a formula like :

If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value< "6:00 AM") Then...

to determine if a given cell occurs between the two listed times? Thanks!

Hi StrangeLuck:
I don't know the context in which you are using this formula, but syntax-wise the following will work

=IF(AND(I5>"12:00 AM"+0,I5<"6:00 AM"+0),"YES","NO")




_________________
Yogi Anand

Edit: Deleted inactive website from hardcoded signature
This message was edited by Yogi Anand on 2003-01-19 14:07
 
Upvote 0
What do you mean by 12:00 AM?

If you want to test if time is between 0:00
and 6:00. Put the criteria in say B1 and B2
and use

=AND(I5>B1,I5<B2)
 
Upvote 0
Hi Dave:
Logic wise, I agree with you. However in the original post, StrangeLuck had asked if he could directly use 12:00 AM and 6:00 AM in an IF formula, that is why response to him that I don't know in what context he asked the question, but syntax-wise 12:00 AM and 6:00 AM could be directly used in the formula.

Regards!

Yogi Anand
 
Upvote 0
If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

How can a value be both greater than 12:00
and less than =6:00?
 
Upvote 0
On 2002-04-22 16:43, Dave Patton wrote:

If (Cells(i,5).Value > "12:00 AM") And (Cells(i,5).Value < "6:00 AM") Then...

How can a value be both greater than 12:00
and less than =6:00?

Dave: It does appear to be strange, however in my formula

="12:00 AM"+0 is coerced into 0.00
and
="6:00 AM"+0 is coerced into 0.25

Regards!
Yogi Anand
 
Upvote 0
Sorry, I should have been clearer in my question, I suppose. I am looking for a VBA method whereby I could determine if a value in a certain cell, say B1, is between 12:00 AM and 6:00 AM. Thanks!
 
Upvote 0
Do not use 12:00 AM

consider

with cell references =AND(I5>B1,I5<B2)
or
=AND(I5>"6:00"+0,I5<"12:00"+0)

or =AND(I5>0.25,I5<0.5)
 
Upvote 0
On 2002-04-23 07:51, Dave Patton wrote:


Do not use 12:00 AM

consider

with cell references =AND(I5>B1,I5<B2)
or
=AND(I5>"6:00"+0,I5<"12:00"+0)

or =AND(I5>0.25,I5<0.5)

But, I wanted it to be a VBA solution, including the AM.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,192
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top