Help with Formula

[AdamHassig]

Hello, wondering if someone could help me correct the formula in the photo.  I am hoping for this to happen:

If OLD JOB# is blank, display current 2 digit year (CREATED DATE) & "-" & 4000 + ID#. 

If OLD JOB# is NOT blank, display OLD JOB#

For example, looking at the last 2 rows at the bottom, 42 is correct, but 43 should display "24-4183"

[TheTimeSavingCo · Accepted solution]

Try this:

IF(
  {Old Job #},
  {Old Job #},
  RIGHT(
    YEAR({Created Date}) & "",
    2
  ) & "-" & (4000 + {ID #})
)

[AdamHassig]

I was able to make this work.  Thank you for your help!

[jsep]

An explanation of why the formula was failing:

The first argument of the "RIGHT" text operator requires a string. However, YEAR({Created Date}) returns a number. To fix this, you need to convert the result of YEAR({Created Date}) to a string. You can do this using the & operator and adding "".

For example:

RIGHT(YEAR({Created Date}) & + '', 2)
                           ^^^^^^ this part does the trick 

I hope this helps! If you need assistance with implementation or anything else, feel free to schedule a 15min call with me