Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get
1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
=IF(B40,B4+180,"") On Fri, 10 Sep 2004 18:42:04 -0400, "Dennis Allen" wrote: Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get 1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Change the formula to =IF(B40,B4+180,"") which will make the cell blank
"Dennis Allen" wrote in message ... Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get 1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Thanks...Dennis
"bigwheel" wrote in message ... Change the formula to =IF(B40,B4+180,"") which will make the cell blank "Dennis Allen" wrote in message ... Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get 1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Thanks...Dennis
"Myrna Larson" wrote in message ... =IF(B40,B4+180,"") On Fri, 10 Sep 2004 18:42:04 -0400, "Dennis Allen" wrote: Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get 1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Hi again. There's a problem. If I have D5 with the formula =IF(B40,B4+180,""). then another column with a formula
=IF((D50)*(F5(D5+180)),"PAST DUE","-") it won't work. How can I use D5, which might be a date or a ""? "Myrna Larson" wrote in message ... =IF(B40,B4+180,"") On Fri, 10 Sep 2004 18:42:04 -0400, "Dennis Allen" wrote: Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get 1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Hi again. There's a problem. If I have D5 with the formula =IF(B40,B4+180,""). then another column with a formula
=IF((D50)*(F5(D5+180)),"PAST DUE","-") it won't work. How can I use D5, which might be a date or a ""? "bigwheel" wrote in message ... Change the formula to =IF(B40,B4+180,"") which will make the cell blank "Dennis Allen" wrote in message ... Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B40,B4+180,0), however, I get 1/0/1900. Question. Is there any way to surpress 1/0/1900? If there's no value, I want to see an empty cell. Thanks for any advice...Dennis |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
just use another if conditiion to check if D5 has a date or a "", an
accordingly set the value. - Manges -- Message posted from http://www.ExcelForum.com |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
"mangesh_yadav " wrote in message ...
just use another if conditiion to check if D5 has a date or a "", and accordingly set the value. How? Two question come to mind. One, does excel have a function to determine a cell's type, be it date or text? In JavaScript you have typeof(). Two, does excel stop conditional testing? In Javascript, if you have if (typeof(a)=="string" & a < "g"), if the variable 'a' is numeric you never see a < "g" executed...Dennis |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Excel evaluates all the conditions.
I can't see the whole thread, so I don't know the specifics, but you can use isdate to see if a value can be interpreted as a date. Excel has a typeof construct if type of object1 is object2 Then there is also vartype But for most tests, a date is a number, so there is no way to differentiate a number from a date for the most part. -- Regards, Tom Ogilvy "Dennis Allen" wrote in message ... "mangesh_yadav " wrote in message ... just use another if conditiion to check if D5 has a date or a "", and accordingly set the value. How? Two question come to mind. One, does excel have a function to determine a cell's type, be it date or text? In JavaScript you have typeof(). Two, does excel stop conditional testing? In Javascript, if you have if (typeof(a)=="string" & a < "g"), if the variable 'a' is numeric you never see a < "g" executed...Dennis |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Well, the problem is a cell referencing another cell, of type date, displaying 1/0/1900. I could use IF(B33<0,B33,""), but then
other cells would have to test for either a date or a string. To avoid the whole mess, I'll just create a hidden column for the date and the displayed column will do the IF() test...Dennis "Tom Ogilvy" wrote in message ... Excel evaluates all the conditions. I can't see the whole thread, so I don't know the specifics, but you can use isdate to see if a value can be interpreted as a date. Excel has a typeof construct if type of object1 is object2 Then there is also vartype But for most tests, a date is a number, so there is no way to differentiate a number from a date for the most part. -- Regards, Tom Ogilvy "Dennis Allen" wrote in message ... "mangesh_yadav " wrote in message ... just use another if conditiion to check if D5 has a date or a "", and accordingly set the value. How? Two question come to mind. One, does excel have a function to determine a cell's type, be it date or text? In JavaScript you have typeof(). Two, does excel stop conditional testing? In Javascript, if you have if (typeof(a)=="string" & a < "g"), if the variable 'a' is numeric you never see a < "g" executed...Dennis |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
Wouldn't the other cells have to test for a zero anyway? Since you don't
seem to accept 0 as a valid date, then it seems the other cells/formulas shouldn't either. -- Regards, Tom Ogilvy "Dennis Allen" wrote in message ... Well, the problem is a cell referencing another cell, of type date, displaying 1/0/1900. I could use IF(B33<0,B33,""), but then other cells would have to test for either a date or a string. To avoid the whole mess, I'll just create a hidden column for the date and the displayed column will do the IF() test...Dennis "Tom Ogilvy" wrote in message ... Excel evaluates all the conditions. I can't see the whole thread, so I don't know the specifics, but you can use isdate to see if a value can be interpreted as a date. Excel has a typeof construct if type of object1 is object2 Then there is also vartype But for most tests, a date is a number, so there is no way to differentiate a number from a date for the most part. -- Regards, Tom Ogilvy "Dennis Allen" wrote in message ... "mangesh_yadav " wrote in message ... just use another if conditiion to check if D5 has a date or a "", and accordingly set the value. How? Two question come to mind. One, does excel have a function to determine a cell's type, be it date or text? In JavaScript you have typeof(). Two, does excel stop conditional testing? In Javascript, if you have if (typeof(a)=="string" & a < "g"), if the variable 'a' is numeric you never see a < "g" executed...Dennis |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date cell format
The hidden column will have the numeric date. Other cells will use that hidden column date in their formulas. The displayed date
column will do the IF(B33<0,B33,"") and will not be used in any formulas...Dennis "Tom Ogilvy" wrote in message ... Wouldn't the other cells have to test for a zero anyway? Since you don't seem to accept 0 as a valid date, then it seems the other cells/formulas shouldn't either. -- Regards, Tom Ogilvy "Dennis Allen" wrote in message ... Well, the problem is a cell referencing another cell, of type date, displaying 1/0/1900. I could use IF(B33<0,B33,""), but then other cells would have to test for either a date or a string. To avoid the whole mess, I'll just create a hidden column for the date and the displayed column will do the IF() test...Dennis "Tom Ogilvy" wrote in message ... Excel evaluates all the conditions. I can't see the whole thread, so I don't know the specifics, but you can use isdate to see if a value can be interpreted as a date. Excel has a typeof construct if type of object1 is object2 Then there is also vartype But for most tests, a date is a number, so there is no way to differentiate a number from a date for the most part. -- Regards, Tom Ogilvy "Dennis Allen" wrote in message ... "mangesh_yadav " wrote in message ... just use another if conditiion to check if D5 has a date or a "", and accordingly set the value. How? Two question come to mind. One, does excel have a function to determine a cell's type, be it date or text? In JavaScript you have typeof(). Two, does excel stop conditional testing? In Javascript, if you have if (typeof(a)=="string" & a < "g"), if the variable 'a' is numeric you never see a < "g" executed...Dennis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Format Overrides to Date Format | Excel Worksheet Functions | |||
Cell Format - Date | Excel Discussion (Misc queries) | |||
How can I get date of file creation to XLS cell in date format? | Excel Worksheet Functions | |||
How do I format a cell to a date format? | Excel Discussion (Misc queries) | |||
cell format for date/time in same cell excel 2003 | Excel Worksheet Functions |