Excel 2007 conditional formula...??

[dlorenc]

What is wrong with this formula?..I am getting the 'the formula you typed contains an error"...grrrr..


=and(now()<f4
,b4<1)

f4 is a date field = 6/30/09, and b4 is a number field containing .71...

 

[nbrcrunch]

the formula works fine for me. but the first half of the AND statement doesn't make any sense. NOW() will return both the date and time for right now. How could that ever be false?

perhaps you intended:
=AND(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))=F4,B4<1)

 

[dlorenc]

sorry....typing that formula in here seems a bit difficult...

=and (now()<f4, b4<1)

=and(now() < f4...

, b4<1)

 

[nbrcrunch]

see edit above.

 

[mole999]

I'm guessing that if F4 is greater than today AND B4 is less than 1 (TRUE), is that right

 

[dlorenc]

I'm guessing that if F4 is greater than today AND B4 is less than 1 (TRUE), is that right

yes....

but I think I have a bigger problem..I get that formula error message on your recommendation too..

=AND(DATE(YEAR(NOW()),MONTH(NOW()),DAY(NOW()))=F4,B4<1)

This happened to me last week when I tried to add a function in VB....that function works on other co-workers excel implementation but not mine...my IT guys solution was to reload office..which was done....

I have something else going on that is screwing up formulas (functions..?)...

why me??? why always ME!!!!

 

[dlorenc]

is there a way to post an image?...or if you dont mind an email...I can send the simple case...

I'm thinking I must have some weird feature turned on?..off?...

If I split the problem into two parts...it calcs correctly...if I use the and logic, it blows up...

grrrrr

 

[dlorenc]

Book1
	A	B	C	D	E	F	G	H
1	0.71		6/30/2009		=AND(now()=c1,a1<1)
2
3	0.71		6/30/2009		TRUE
4
5	0.71		6/30/2009		TRUE
6
Sheet1

 

[dlorenc]

you folks are not gonna believe this one...

I figured it out…..

1. I had an effort to prepare a .csv file for a vendor. But they required a ‘|’ rather than a ‘,’ for a delimiter. In 2007, to change the delimiter in a saved .csv file, Microsoft added that function to the control panel/regional settings. What I missed was the little note in italics down at the bottom…

Change the separator in all .csv text files
1. In Microsoft Windows, click the Start button, and then click Control Panel.
2. Open the Regional and Language Options dialog box.
3. Do one of the following:
- In Windows Vista, click the Formats tab, and then click Customize this format.
- In Windows XP, click the Regional Options tab, and then click Customize.
4. Type a new separator in the List separator box.
5. Click OK twice.

NOTE After you change the list separator character for your computer, all programs use the new character as a list separator. You can change the character back to the default character by following the same procedure.

2. So after changing the delimiter, I never changed it back…soooo…the ‘list separator’ includes any excel argument list as well… which is used in most formulas….soooo…my function (and in fact ALL my formulas) were failing because excel was expecting to see the new delimiter in the argument list….it wanted a '|' not a ',' …..

=colorfunction(B34,b34:b38,false) failed…
=colorfunction(B34|b34:b38|false) worked….

Go figure…grrrrrrrr…

Thought you might like to know this subtle nuance of 2007…*smile*…

 

[Greg Truby]

Thanks for letting us know -- that's one of those little conundrums that can drive you quite mad until you finally figured out what's going on.