- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 10, 2024 07:48 PM
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"
Solved! Go to Solution.
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 10, 2024 08:45 PM
Try this:
IF(
{Old Job #},
{Old Job #},
RIGHT(
YEAR({Created Date}) & "",
2
) & "-" & (4000 + {ID #})
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 10, 2024 08:45 PM
Try this:
IF(
{Old Job #},
{Old Job #},
RIGHT(
YEAR({Created Date}) & "",
2
) & "-" & (4000 + {ID #})
)
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 11, 2024 05:34 AM
I was able to make this work. Thank you for your help!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎Apr 11, 2024 07:07 PM
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
![](/skins/images/FE00829FDD2AE889FAB731D8F02A8942/responsive_peak/images/icon_anonymous_message.png)