Conditional value labels for missing values

[#1]

I wonder if there's a way to set up conditional value labels based on values from two columns. If I refer to the snapshot table below, I have "column 1" which could either be filled or empty and I also have "column 2" which either be filled or empty. What I'm trying to do is for "column 2" to be formatted like "Column 3". 

Which is: (i) values formatted to a date format if there's a value AND the "column 1" value on the same row is not empty, (ii) To display "Not Credible" if the "Column 2" value is missing but "Column 1" value on the same row is not empty, and (iii) to remain as "." or empty if both "column 1" & "column 2" values on the same row are missing. 

Appreciate the help. 

[#2]

Hi, 

The task you presented to us is a bit tricky because you wont both text "Not Credible" and Dates in the same column. Thus, assuming that your Column 2 dates are entered as numbers (no leading "0" for month < 10) and that the order of the day - month - year is always the same, here is a formula that should do the trick:

DATEIN = Char( :Column 2 );
DATEOUT = "";
If( !Is Missing( :Column 1 ),
	If( !Is Missing( :Column 2 ) & Is Number( :Column 2 ),
		If(
			Length( DATEIN ) == 8,
				DATEOUT = Format( Informat( DATEIN, "mmddyyyy" ), "ddMonyyyy" ),
			Length( Char( DATEIN ) ) == 7,
				DATEIN = "0" || DATEIN;
				DATEOUT = Format( Informat( DATEIN, "mmddyyy" ), "ddMonyyyy" );
		)
	,
		DATEOUT = "Not Credible"
	)
,
	DATEOUT = "."
);
DATEOUT;

I intentionally used local variable to make the formula more readable but it is not really required.

Let us know if that works for you.

Best,

TS

[#3]

Thanks @Thierry_S. If I understand correctly, this would make the "column 2" column type to be "character", right ? 

What if I want to keep it as Numeric for further analysis ? The whole idea of differentiating blank and "not credible" is just for filtering purposes. I know I can make a new column for that but I wonder if there are other ways. 

[#4]

Hi,
You do not need to transform Column 2 into character: I took care of it in the formula (see: Char (Column 2) statement in the formula).
Did you try to use the formula I shared and did it yield the results you expected?
Let me know if you need help with the implementation of this potential solution.
Best,
TS

[#5]

I tried to paste the formula you provided into "Column 2" column formula but it gave me an error. Perhaps I misunderstood your guidance. 

[#6]

Hi,
Sorry for the confusion: the formula is intended to be added to a third column and then referring to Column 1 and Column 2; does that make sense?
Best,
TS