Date switches to general in IF func in Excel

August 2, 2011 at 11:14:04
Specs: Windows 7

I'm having trouble writing an IF function for cells formatted as dates. Following is a brief description:

I've got a column with dates ( J5:14; all cells in date format). I then have another date in a separate cell (e.g., 7/31/11 in R5). When I write an IF statement (=IF($R$5=J$5:J$14,"True","False")) and then drag it down, it works for the first couple of rows and then returns "False" even when true, and then finally "#value" for the last few cells.

When I look at the calculation steps for the cells where "#value" is returned, it looks as though R5 gets treated as "general" rather than "date", so that IF(7/31/11=J$5:J$14) is no longer the case but rather IF(40745=J$5:J$14) and this is of course why excel magic doesn't work...

Please help.

See More: Date switches to general in IF func in Excel

August 2, 2011 at 12:31:32
Where are you putting this formula?

If I put it in any column starting in Row 5, it works fine unless I drag it down past Row 14 (or above Row 5). That's when I get a #VALUE error which I would expect.

When you use the $R$5=J$5:J$14 syntax, the formula will only work correctly in Rows 5:14, otherwise the J$5:J$14 portion evaluates to #VALUE, which is why the entire formula evaluates to #VALUE.

Have you looked at the calculation step when the formula works? I would be surprised if the date didn't appear as 40745 in every case since that is how Excel stores a date internally.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 2, 2011 at 13:02:45
Dear DerbyDad03,

Thanks for your response. Indeed, it works just fine if I drag the formula in rows adjacent to $J$5:$J$14.

This leads me to a couple of other questions:

1. I'm thinking that I need the range to be $J$5:$J$14 because if I leave it as simply J5:J14, when I drag the IF function, the range will be altered. Am I correct in thinking this way?

2. How can I get the IF function to return the correct results in a different location (not adjacent to me specified range), e.g., in a different sheet?

This is important for me to understand because what I'm ultimately trying to do is combine this with other functions to create a formula that will return multiple entries from a list that match today's date. (The formula I've come up is =INDEX($J$5:$O$15, SMALL(IF($R$5=$J$5:$J$14, ROW($J$5:$J$14)-MIN(ROW($J$5:$J$14))+1, ""), ROW(J4)), COLUMN(J4)) but it also returns "#value!" I suspect because I'm somehow specifying an invalid range).

Any advice you can give will be very helpful.

Report •

August 2, 2011 at 19:05:12
Well, for one thing, I've never used the "range" syntax in an IF statement.

To be honest, I'm not even sure what it accomplishes. What's wrong with just using =IF(J5=$R$5, "True", "False") and dragging it down to increment the Row?

That syntax can be used in any Row on any sheet.

The "range" syntax only evaluates 1 cell at a time anyway and is restricted to Rows 5:14 to work properly, so what is the advantage? I'll admit I'm not very familiar with it and had never seen it before until someone else posted it in this forum - and had a problem with it. Where did you learn to use it and what does the use of the "range" accomplish?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

August 2, 2011 at 21:10:46
Well, I didn't actually learn it. I found a post by someone (after much searching) which showed a nice example of the larger formula which does exactly what I want. The only problem is that I can't get it work... I found it at

There's nothing wrong with "=IF(J5=$R$5, "True", "False")" but it doesn't do what I need it to.

To my understanding, the "range" here is used to do just what you mentioned - restrict the values to a particular row because later, the SMALL and INDEX functions build on operations performed on the specific rows.

In lay language I think this is what's happening overall:
1. I specify a range and ask the IF function to evaluate the logic;
2. For true values, I then ask the IF function to return the number of that row minus the smallest row number within this fixed range +1; for false ""
3. I then ask it to return the k-th smallest value of the row for which there was a "true" match (SMALL);
4. I then ask it to return the value of the cell (INDEX);

All of this to do what DGET does but for getting multiple entries when searching based on one criterion. If you know of an easier way to accomplish please share it because this is the closest I've gotten.

Report •

August 9, 2011 at 12:14:56
Do you think you can help me with a date issue too, please?

How do I format a date as mm/dd/yyyy to read yyyy/mm/dd?

Report •

August 9, 2011 at 14:28:59

Your question is not related to the subject matter of this thread. If unrelated questions and answered get all mixed up in one thread, the searchable archives would be of no use to anyone.

Please post your question as a new question with a relevant subject line.

Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

August 9, 2011 at 14:34:45

I'm having trouble deciphering your needs.

Would it be possible for you to post some example data along with an explanation of what you are trying to accomplish?

If you are going to post data, please click on the following line and read the instructions given via that link. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Ask Question