check if or not time lies between a time interval

[Jaspreet281]

i have different entry times with me and i want to know whether the
were between 10 pm to 7 am,

so if i have an entry time of 1.40 am, the formulla should return m
"incorrect"
and
say if the ebtry time is 10.00am, the formulla should return m
"correct"

Can anyone please help me with this!!

 

[Pete_UK]

If your entry time is in A1 in Excel time format, put this formula in
B1:

=IF(AND(A1>=7/24,A1<=22/24),"correct","incorrect")

Note that times are stored in Excel as fractions of a 24-hour day,
which explains why I have written the comparators as 7/24 and 22/24.

Hope this helps.

Pete

 

[Jaspreet281]

Thanks Pete, but watever time i choose in the excel its only showing i
correct,

for instance i appliad it on 09.48am it still returned me Incorrect
actually this should be correct, sceondly as i mentioned what ever tim
i am taking the end result is incorrect only,

my target cell is in this format 11/5/2011 9:48:46 AM and its a 24hr
format.

Please suggest !

;1600939']If your entry time is in A1 in Excel time format, put thi
formula in
B1:

=IF(AND(A1>=7/24,A1<=22/24),"correct","incorrect")

Note that times are stored in Excel as fractions of a 24-hour day,
which explains why I have written the comparators as 7/24 and 22/24.

Hope this helps.

Pete

i have different entry times with me and i want to know whether they
were between 10 pm to 7 am,

so if i have an entry time of 1.40 am, the formulla should return me
"incorrect"
and
say if the ebtry time is 10.00am, the formulla should return me
"correct"

Can anyone please help me with this!!!

 

[Claus Busch]

Hi Jaspreet,

Am Thu, 19 Apr 2012 05:00:36 +0000 schrieb Jaspreet281:

for instance i appliad it on 09.48am it still returned me Incorrect,
actually this should be correct, sceondly as i mentioned what ever time
i am taking the end result is incorrect only,

my target cell is in this format 11/5/2011 9:48:46 AM and its a 24hrs
format.

try:
=IF(OR(ROUND(MOD(A1,1),2)>=22/24,ROUND(MOD(A1,1),2)<=7/24),"incorrect","correct")


Regards
Claus Busch

 

[joeu2004]

my target cell is in this format 11/5/2011 9:48:46 AM
and its a 24hrs format.

=IF(OR(HOUR(A1)<=7,22<=HOUR(A1)),"between","not between")

Change "<=" to "<" if you do not want to include 10pm and 7am.