Skip to main content
Solved

Formula help

  • March 15, 2023
  • 2 replies
  • 35 views
D
Forum|alt.badge.img+2

Hi, 

Using the formula below I have an ERROR value issue when there is no date value available for {last deal stage change}. Can anyone advise how I would alter this to just leave a blank space or "no data" where there is no date value available in the date field column?

I need to use the datetime format to tidy up and remove the timestamp 

TRIM({Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & DATETIME_FORMAT({last deal stage change},'DD/MM/YY') & " | " & {Deal Stage})

Thanks!

Best answer by Ben_Young1

Hey @Donna11,

Here are two options:

If you want it to just skip it entirely if the field is blank, then you can use this:

TRIM( {Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & IF( {last deal stage change}, DATETIME_FORMAT( {last deal stage change}, 'DD/MM/YY' ) ) & " | " & {Deal Stage} )

If you want it to return "No Data", then you could use this:

TRIM( {Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & IF( {last deal stage change}, DATETIME_FORMAT( {last deal stage change}, 'DD/MM/YY' ), "No Data" ) & " | " & {Deal Stage} )

 

2 replies

Ben_Young1
Forum|alt.badge.img+22
  • Ben_Young1
  • Brainy
  • Answer
  • March 15, 2023

Hey @Donna11,

Here are two options:

If you want it to just skip it entirely if the field is blank, then you can use this:

TRIM( {Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & IF( {last deal stage change}, DATETIME_FORMAT( {last deal stage change}, 'DD/MM/YY' ) ) & " | " & {Deal Stage} )

If you want it to return "No Data", then you could use this:

TRIM( {Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & IF( {last deal stage change}, DATETIME_FORMAT( {last deal stage change}, 'DD/MM/YY' ), "No Data" ) & " | " & {Deal Stage} )

 

D
D
Forum|alt.badge.img+2
  • Donna11
  • Author
  • New Participant
  • March 15, 2023

Hey @Donna11,

Here are two options:

If you want it to just skip it entirely if the field is blank, then you can use this:

TRIM( {Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & IF( {last deal stage change}, DATETIME_FORMAT( {last deal stage change}, 'DD/MM/YY' ) ) & " | " & {Deal Stage} )

If you want it to return "No Data", then you could use this:

TRIM( {Status symbol} & " | " & {Pipeline} & " | " & "Stage last changed: " & IF( {last deal stage change}, DATETIME_FORMAT( {last deal stage change}, 'DD/MM/YY' ), "No Data" ) & " | " & {Deal Stage} )

 


Thank you @Ben_Young1  all fixed. I really appreciate your help with this!

I'm still finding my way with formulas and nesting 🙂

Terms & ConditionsAccessibility statement