If date is between then add 1

Hello,

I have a questions why the following formula does not work:

"=IF(AND(Sheet2!A14:A300>AF1;Sheet2!A14:A300<AH1;TEXT(Sheet2!A14:A300;"dddd")=X3);1;0)

Sheet2!A14:A300 are dates values between t.e 1.1.2014-31.12.2014.
AF1 is the start date
AH1 is the end date
X3 is either "Mo,Tu, We.."

The function works if date range is not added, so with only "text()-function."

Any ideas?

Cheers,


T

Originally Posted by FDibbins

Couple of shots in the dark - hard to say without seeing this with some data....

1. that looks like an ARRAY formula, did you enter it using CTRL SHFT ENTER?
2. You said X3 is either "Mo,Tu, We.., but your TEXT() is DDDD?
3. Are your dates actual dates, and not text that looks like a date? Test with =ISNUMBER(cell-ref)...FALSE indicates text

Hi!

Thanks for the reply. Heres my answers:

1. I used array, so it put up with ctrl shift enter.
2. It was mistake X3 is monday, tuesday, etc. Not mo, tu, we etc., sorry
3. Dates are actual, so the mentioned test gives true.

I just don't get it :/

Banaticus t.e is e.g, in swedish though.

Regards,

T

Hmph None can not find the error in the formula? I sure do not, as all seems ok. The formula also works if the range is removed, so if you try with only Sheet2!A4 in both of the ranges it will work and adds 1.

But I don't know what could be wrong with the range?

As said earlier the range works also with only the Text() formula, so it seems that there is something wrong with the date range -thing. And for the information the outcome is "0", not an error.

Best Regards,


T

What is t.e?

What is Sheet2!A14:A300>AF1 does that mean Excel is supposed to compare every date in the A column, all 296 of them with the start date? That could be your problem. Set up a partially fixed reference, perhaps something like:
Sheet2!$A14>AF1 or something else where only a single cell is being compared at a time. Same goes for TEXT(Sheet2!A14:A300;"dddd")

"1. that looks like an ARRAY formula, did you enter it using CTRL SHFT ENTER?"

Hi,

As I said, the fuction works if the date range is taken of and use only single cell reference.

But even though, I'd need it from the whole range, not just from one cell.

The range is not a problem with the text() part as it works flawlesly So the problemm is not in the range, for someone reason it seems like Excel does not recognize the range or something like that.