Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Help with Formula

Topic Labels: Formulas
Solved
Jump to Solution
947 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