Feb 01 2021 08:37 AM
Hello everyone
I am dealing with this formula =AND(AA9>=AD9, AA9<=Z9). I have a time and I want to see if it is within 2 other times. The part that is driving me nuts is sometimes this is returning a True statement when I know it is not.
Currently the times are 15:30:00 and 17:00:00 and excel is saying 12:00:00 is true statement and found between those times. I must be doing something wrong in the format or something. Please help.
Thanks
Jaime
Feb 01 2021 08:56 AM
What happens if you format AA9, AD9 and Z9 as General? Which values do you see?
Feb 01 2021 08:59 AM
Feb 01 2021 09:28 AM
Feb 01 2021 10:13 AM
Feb 01 2021 11:27 AM
If nothing changed, that suggests that the values are not real time values, but text values.
Do the following:
Feb 01 2021 11:27 AM
Could you please provide sample file without sensitive information?
Feb 01 2021 11:33 AM
That helps. It is a format issue. I had just took every column and made them plus 0 which made the formats the same and it works. Very similar to what you said to do. I just don't know how to do what you said in bulk instead of one by one. Thank you so much for the help.
Feb 01 2021 11:36 AM
You can do the following:
Feb 01 2021 11:41 AM
Thank you for your time. It was a formatting issue. This might not be the best way to correct it but I took the columns in questions and did =column+0 and that gets them in the same format. Then the formula worked!!!
Feb 01 2021 11:51 AM
That means values are texts. =(text which presents number/time)+0 returns that number/time.
Only format applying is not enough for such conversion, you shall re-enter value or apply arithmetic as above, after use desired format if necessary.