Getting the last 2 characters of the entry date | Community
Skip to main content
RandyRoberts
Community Advisor
RandyRobertsCommunity Advisor
Community Advisor
May 13, 2020
Solved

Getting the last 2 characters of the entry date

  • May 13, 2020
  • 2 replies
  • 1349 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by skyehansen

    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. :)

    2 replies

    N
    NRYNR
    Level 10
    May 13, 2020

    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
    Community Advisor
    Doug_Den_Hoed__AtAppStoreCommunity Advisor
    Community Advisor
    May 13, 2020

    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
      Community Advisor
      RandyRobertsCommunity AdvisorAuthor
      Community Advisor
      May 13, 2020

      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

      RandyRoberts
      Community Advisor
      RandyRobertsCommunity AdvisorAuthor
      Community Advisor
      May 13, 2020

      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.

      Terms & ConditionsAccessibility statement

      Loading footer...