WEEKDAY Formula returning #NAME? 1

[Airbisk]

Hi,

The formula below should look to see if today is a Monday and if cell K2 is less than 3 return 1, if not then return blank.

=IF((AND(WEEKDAY(TODAY())=2,K2<3)),1,IF(K2=1,1,””))

All I get back is #NAME? and I don't understand why, can anyone guide me in the right direction. I'm using Excel 2007.

Many Thanks
Steve

 

[kjv1611]

Is your data in cell K2 formatted as text?

I tried your formula here, and put diff values in K2, and it seems to work to the expectations.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Airbisk]

Thanks for looking kjv,

The formatting is a number, the formala comes back with #NAME? when its run on a different day to Monday which I can live with. I'm recording a macro so can I get rid of those errors with a find and replace.

Thanks again

Steve

 

[kjv1611]

Well, today is Thursday where I'm at. I ran it, no errors whatsoever. So it's not just the day thing, I don't think.

Have you tried pulling different parts of the formula out just to see? I'd suggest trying only the part that should be working, for now, and see what it gives you.

So, I'd try:

=IF(K2=1,1,"")

If that works by itself, then you know it's a problem with the first condition, or else the AND.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Airbisk]

If I put a space between "" or change it to 0 then #NAME? disappears. i.e.

=IF(K2=1,1," ") or
=IF(K2=1,1,0)

The two conditions work, it doesn't like if both conditions are false unless you do similar to the above.

 

[SkipVought]

In the FORMUAL BAR, select WEEKDAY(TODAY()) in your formual and hit F9. It will try to evaluate this expression.

If this returns the #NAME error, you need to reinstall Excel as this function is missing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[kjv1611]

I did not know the F9 trick... or else forgot already. Thanks for mentioning that, Skip1

--

"If to err is human, then I must be some kind of human!" -Me