Date cell format

[Dennis Allen]

Hi. In a date column, if I leave the cell empty nothing is displayed. If I add a formula =IF(B4>0,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

 

[Myrna Larson]

=IF(B4>0,B4+180,"")

Hi. In a date column, if I leave the cell empty nothing is displayed. If I

add a formula =IF(B4>0,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

 

[bigwheel]

Change the formula to =IF(B4>0,B4+180,"") which will make the cell blank

Hi. In a date column, if I leave the cell empty nothing is displayed. If

I add a formula =IF(B4>0,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

 

[Dennis Allen]

Thanks...Dennis

Change the formula to =IF(B4>0,B4+180,"") which will make the cell blank


I add a formula =IF(B4>0,B4+180,0), however, I get
no value, I want to see an empty cell. Thanks for any

 

[Dennis Allen]

Thanks...Dennis

=IF(B4>0,B4+180,"")



add a formula =IF(B4>0,B4+180,0), however, I get
value, I want to see an empty cell. Thanks for any

 

[Dennis Allen]

Hi again. There's a problem. If I have D5 with the formula =IF(B4>0,B4+180,""). then another column with a formula
=IF((D5>0)*(F5>(D5+180)),"PAST DUE","-") it won't work. How can I use D5, which might be a date or a ""?

 

[Dennis Allen]

Hi again. There's a problem. If I have D5 with the formula =IF(B4>0,B4+180,""). then another column with a formula
=IF((D5>0)*(F5>(D5+180)),"PAST DUE","-") it won't work. How can I use D5, which might be a date or a ""?

 

[mangesh_yadav]

just use another if conditiion to check if D5 has a date or a "", an
accordingly set the value.

- Manges

 

[Dennis Allen]

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

 

[Tom Ogilvy]

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

"mangesh_yadav >" <<[email protected]> wrote in

message news:[email protected]...

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

 

[Dennis Allen]

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]

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

Well, the problem is a cell referencing another cell, of type date,

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

 

[Dennis Allen]

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