IF AND function not working.. maybe Mac issue?

Hey all!

Thanks for taking the time to read this. I am pretty good at excel, but this one is stumping me. I think it might ultimately be a compatibility issue, as I run Mac Excel 2011. I have a column (let's say A) of times properly formatted, so 2pm is 14:00:00. In the column next to it (let's say B) I am writing a logic equation to determine if the value in column A is between a certain time... in this case between 10 and 14. If it is, simply say so; otherwise, tell me it's not.

This is my code:

=IF(AND(A1>=TIME(10,0,0),A1<=TIME(14,0,0)),"between 10 and 14","not between 10 and 14")

It always comes up with the null, or "not between 10 and 14". I have back tested simpler logic and it works fine, but for some reason it breaks once I put the range of times in there. Can someone run this in a different version of Excel, or tell me what is broken here?

Thanks so much! I thought I was going crazy but now I'm pretty convinced it's a Mac issue

Originally Posted by joeu2004

Probably not! What does =ISTEXT(A1) return?

IN addition to that question..

Is the value in A1 a TIME ONLY value, or does it also have a DATE value?

Try
=IF(AND(MOD(A1,1)>=TIME(10,0,0),MOD(A1,1)<=TIME(14,0,0)),"between 10 and 14","not between 10 and 14")

Ah ha! It indeed returns true. I manually formatted that column to be Time from the dropdown formatting options, but your text test is passing. What, then, is the most assured way to make the formatting time?

It is just a time value. The value returns TRUE.

Originally Posted by sourabhg98

"times are properly formatted" it should not be a formatting issue...

Just because a cell is 'Formatted' as a time, does not mean that cell actually 'contains' a real time value.


@jj

Try copying a blank cell
Highlight the column of times
Right Click - Paste Special - Values - Add - OK

This worked perfectly... but why the hell would the Excel formatting of Time just be nominal, and not actual? What did I do by following your instructions to change the true formatting to Time?

The value you had in A1 was just a Text String in Excel's mind (if it has such a thing), it wasn't really a true Time Value.
It's called a "Number Stored As Text"
A numeric value that is being treated as a Text String.

WHY that cell was considered TEXT, I don't know. How did the value originally get into A1 ?

And changing the format of a cell is really only for appearnces, for your eye's sake.
It just changes the way values 'appear' in the cell.
It does not actually change the value contained in the cell.

An easy way to 'convert' a Number Stored As Text into a Real Number is to simply Add 0 to it
So you could just do =A1+0
But that would be a pain to do to all the values in the column.

The method I showed is a shortcut to do them all at once.

A blank cell is usually given the numeric value of 0
So copying a 0, and doing Paste Special - Values - Add
Adds 0 to the values in the column
Thus converting them to real numeric values.

Hmm no idea. I guess in banking we don't deal with dates like this too frequently. For the record, the times were generated with a random function:

=TEXT(RAND()*(23-10)/24+10/24,"HH:MM:SS ")

Originally Posted by jjthaeler

=TEXT(RAND()*(23-10)/24+10/24,"HH:MM:SS ")

And that's why it was treated as text, because you told it to.
The TEXT function returns a value as....you guessed it....TEXT.

Try changing that to
=TEXT(RAND()*(23-10)/24+10/24,"HH:MM:SS ")+0

Or remove the text function completely
=RAND()*(23-10)/24+10/24

And just format the cell as time (which you did anyway)