Help

Help with Formula

Topic Labels: Formulas
Solved
Jump to Solution
258 3
cancel
Showing results for 
Search instead for 
Did you mean: 
AdamHassig
5 - Automation Enthusiast
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"

Screenshot 2024-04-10 at 10.40.57 PM.png

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try this:

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

Screenshot 2024-04-11 at 11.44.58 AM.png

See Solution in Thread

3 Replies 3
TheTimeSavingCo
18 - Pluto
18 - Pluto

Try this:

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

Screenshot 2024-04-11 at 11.44.58 AM.png

AdamHassig
5 - Automation Enthusiast
5 - Automation Enthusiast

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

 

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