Column calculated with subtraction of two dates

[Francisco Aspillaga]

Hi all,

 

I Generate a calculated column that subtracts the dates. Now I need, depending on the result of this calculated column, to generate another column with the condition: if the result is less than -5, it returns “Time” and if it is greater, it returns “Late”.

I try the following but it gives me an error, how could I achieve it?
 

WHEN [name calculated column] <-5 THEN “Tiempo”

[Tyler Kendle]

Just double checking here, are you using a Case Statement for that When clause?  I typically organize my case statements vertically just to make things easier to read.  So something like this:

Case
When [Dias Prep] IS NULL Then Null
When [Dias Prep]<-5 THEN “Tiempo”
Else "Late"
End

Wondering if your issue is just the Case and End prompts missing... Or perhaps it's because it's a TimeSpan variable, could you change that column to an Integer?

[Francisco Aspillaga]

Hi Tyler,

Thanks for your help!.
 

How could I change the TimeStamp column to Integer?.

 

Regards

 

[Tyler Kendle]

How did you generate the column that subtracted the dates?  Did you use "DateDiff"?  That should return a "real" value which should work fine instead of an Integer.

Here is an example:

I have two columns Today's date, and Date Created.
Step 1:  Calculate Days between the two columns using DateDiff
Formula: DateDiff('day',[Date Created],[Today])

Step 2: Create your new column with your case statement.
Formula: 
Case  
When [Days Open]<5 Then "Tiempo"
Else "Late"
End

Those are just examples, I know your #s are slightly different but the concept should still work for you.

[Gaia Paolini]

I think you can put Integer() around the value to turn it into an integer