Getting the last 2 characters of the entry date

[RandyRoberts]

I've created a field to concatenate 5 fields to create an auto-populated job number. I need Agency Code, Client Code, Product Code, Year (2 characters only), and Job ID (a unique hand-typed string in another field). So far I have

CONCAT(Agency Code,Company.Client Code,Company.Product Code) and that works fine.

I need to add the last 2 characters of the entryDate year next, like 2020="20", 2019="19", etc.

I can't use the expression "RIGHT({entryDate},2)" because RIGHT seems to only work on a string, not a date. There has to be an easy way to achieve this, right?

I thought maybe I have to coerce a date to a string and then grab the right 2 characters but I have no idea where to start with coercion. Is that even a thing in text mode?

Help!

Thanks

[NRYNR]

Hi Randy, what if you RIGHT the STRING format of the date as follows?

RIGHT(STRING({entryDate},2))

I don't know that it will work, but seems logical.

Thanks,

Narayan

[Doug_Den_Hoed__AtAppStore]

Hi Randy,

Similar but slightly different from Narayan's, I'd suggest RIGHT(YEAR({entryDate}),2).

For details on YEAR and all the other juicy bits, I also invite you to bookmark this page, or (if you prefer a pattern), search for CONCAT in 0ne, which seems to bring it back among the top 5 results.

Regards,

Doug

[RandyRoberts]

Hi Doug,

That should work, but doesn't. Shouldn't that parens after the entryDate} be moved to the end? so it's "RIGHT(YEAR({entryDate},2))"? Either way I still get Invalid Expression.

TY

[Doug_Den_Hoed__AtAppStore]

Curious Randy,

YEAR({entryDate}) takes the year portion of the entryDate (e.g. "2020"), and RIGHT(...,2) then takes the rightmost two characters of that result (e.g. "20"). Since entryDate is always a required field, I'd expect that if you set your formula (temporarily) to simply RIGHT(YEAR({entryDate}),2) that you will see results.

Once you do, you can then use CONCAT to (gradually) add each portion, confirming as you do so that you continue to see results, which (just a guess) might look something like this:

CONCAT({DE:Agency Code},{company}.{DE:Client Code},{company}.{DE:Product Code},RIGHT(YEAR({entryDate}),2),{DE:Job ID sequential number suffix})

Regards,

Doug

[RandyRoberts]

How frustrating! That no worky either. It doesn't even matter if I choose text or date for the format.

OK, This is a bit confusing. This sorta works: RIGHT(YEAR(Entry Date),2) but the result is "1/1/70", bizarre!

[skyehansen · Accepted solution]

I am not sure why anyone would choose "date" for the format (in my mind, that would turn everything into a date).

The RIGHT(STRING(Entry Date),2) calc works fine as either a number or a text field (I would tend towards text but that's just because I don't want people adding or av-ing the numbers that come out as a result).

PS: I just realized I should add that the calc gave me 20 as a result, which might be the first or last two digits of this year; who knows. 🙂

[RandyRoberts]

That works! I changed the format to text. It wasn't working with my earlier tries and just never changed it back.

I tested with a project opened in 2019, it works.

Thanks

[NRYNR]

Great news! For those following this thread, I've added a "Calculated Data Expressions" link to the WFPro Training page for quick access among other tools. --Narayan

[RandyRoberts]

Thanks Narayan, That's a good reference. The one good thing I learned here is that you can coerce a date to a string by using STRING. I imagine you can also coerce a string to a number.

[RandyRoberts]

Thanks Narayan,

I just tried this

CONCAT(Agency Code,Company.Client Code,Company.Product Code,RIGHT(STRING({entryDate},2),Job ID sequential number suffix)

but got the dreaded "Custom Expression Invalid. This is an invalid custom expression, please try again.".

it won't even let me save it 😐

If I use "CONCAT(Agency Code,Company.Client Code,Company.Product Code,Job ID sequential number suffix)" it works great, I just need that 2 character year to complete it.

Even if I create a new field called "year concat" and try "RIGHT(STRING({entryDate},2))" it's an invalid expression.