I have a datetime field (e.g. 1/3/2017 11:25:02 PM) and I need to validate if the time falls between 10:00:00 PM (B2) and 6:00:00 AM (C2)).
I tried the following IF statement however doesn't seem to change from yes to no if I modify column A.
=IF(AND(A2>=$B$2,A2<=$C$2)=FALSE,"Yes","No")
Hi Robin, welcome to the MOTH Forum!
I think your problem has to do with two things:
First the way you make the formula is not correct. The '=False' part is...... false (sorry, couldn't help). But you have to remove that one.
Second is the way dates and times behave in Excel. When you enter in A2 a date+time you have to validate that value with other date+times and not only with times! Because for Excel also the number of the date counts and the times in B2 and C2 are always much more less than the number in A2 with the more than 40.000 of the date in it.
In this attachment the solution with the dates also in B2 and C2 (in my case B7 and C7).
Hope this helps!
Frans
Hi Robin,
Please try this:
A3=IF(AND(MOD(A2,1)>=$B$2,MOD(A2,1)<=$C$2+1),"Yes","No")
In excel, one day=24 hours="24:00"=1, use formula MOD("2017/1/3 23:25:02",1) you will get 0.975717593 which equals to “23:25:02”(ignore the date "2017/1/3"). So you can use the result of MOD() to compare with "23:00:00" and "6:00:00". However, "6:00:00" means 6 o'clock of the next day, so you should use "06:00:00"+1.
Hope it helps.
Clark
