Home |
Search |
Today's Posts |
#1
|
|||
|
|||
check if or not time lies between a time interval
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!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check if or not time lies between a time interval
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 On Apr 18, 2:11*pm, Jaspreet281 wrote: 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!!! -- Jaspreet281 |
#3
|
|||
|
|||
Thanks Pete, but watever time i choose in the excel its only showing in 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 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. Please suggest ! Quote:
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check if or not time lies between a time interval
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 -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check if or not time lies between a time interval
Jaspreet281 wrote:
i have different entry times with me and i want to know whether they were between 10 pm to 7 am, "Jaspreet281" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with time interval | Excel Worksheet Functions | |||
x-axis time interval | Charts and Charting in Excel | |||
The best way to specify time interval ... | Excel Programming | |||
Time-interval protection | Excel Programming | |||
Minimum time per interval | Excel Programming |