Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
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 "joe@malvern" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
=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. joe@malvern wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
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 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
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 DATAvaluation 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" wrote: 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 wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
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 wrote: 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 DATAvaluation 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formatting
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")) "Dave Peterson" wrote: 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 wrote: 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 DATAvaluation 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" wrote: 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 wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formatting Conditional Formatting Icon Sets | Excel Discussion (Misc queries) | |||
Formatting cells in a column with conditional formatting? | Excel Discussion (Misc queries) | |||
Protect Cell Formatting including Conditional Formatting | Excel Discussion (Misc queries) | |||
conditional Formatting based on cell formatting | Excel Worksheet Functions | |||
expanding custom formatting without removing existing cell formatting? | Excel Worksheet Functions |