formatting

  • Thread starter Thread starter joe@malvern
  • Start date Start date
J

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
 
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
 
=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.
 
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)
 
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
 
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.
 
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"))
 
Back
Top