# Help with Formula

Topic Labels: Formulas
Solved
258 3
cancel
Showing results for
Did you mean:
5 - Automation Enthusiast

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"

1 Solution

Accepted Solutions
18 - Pluto

Try this:

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

3 Replies 3
18 - Pluto

Try this:

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

5 - Automation Enthusiast

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

6 - Interface Innovator

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 elsefeel free to schedule a 15min call with me