formatting

[joe@malvern]

I have formatted a data entry column for dates as follows:
Category=date
type=03/14/01

how can I format the cell to remain blank after using an IF function?
currently it returns 01/00/00

thanks

 

[Mike H]

Hi,

You could show us the IF function but generally it can be done like this

=if(a1=1,do this,"")

So in this IF formula if A1 doesn't equal 1 the cell is left blank.

Mike

 

[Dave Peterson]

=if(a1="something",today(),"")

The "" returns an empty string.

Your formula is returning a 0 and when you format 0 as a date, you'll see
01/00/00.

 

[Dave Peterson]

And I bet you're using a simple formula like:

=A1
or
=Sheet2!A1

And when those "sending" cells are empty, you'll see 0. And formatting the 0 as
a date, you'll see 01/00/00.

So you could use:

=if(a1="","",a1)
or
=if(sheet2!a1="","",sheet2!a1)

 

[joe@malvern]

Hi Dave,

sorry for the delay. I did not mention that because it is a data entry date
for my sales guys that I formatted through DATA>valuation to only accept a
date between 01/01/09-12/31/09. this way the guys can enter Jan 1 or 01/01
and it defaults to the 00/00/00 format to work with. is there a way to still
enter an IF function in these cells?

thanks for your help if you are still out there

 

[Dave Peterson]

Excel is seeing their input as 01 divided by 01.

Tell those users to toggle a setting...

In xl2003 menus:
Tools|Options|Transition tab|Uncheck all those Lotus 123 transition settings.

 

[joe@malvern]

I can see how the input is viewed as 01/01. However, using xl2003 and do not
see on the transition tab an option to uncheck lotus settings?
I would lke to keep the "DATE" column formatted as is to work with because
the guys would enter Jan 1, January 1, 2009, 01/01, etc..as formatted it will
default to the 00/00/00.
objective is to filter by month (k3:k299), check another array (n3:n299) for
another critera and then sum M3:m299 if both conditions are met


I thought maybe a sumroduct would work but the below retruns 0 yet there are
some date cells (k3:k299) with 01/01/09
=SUMPRODUCT(M3:M299,(K3:K299="01/01/09"))