Get 2-digit value from year

[WilliamPorter]

Have a field InvoicedDate. I want to get the last two digits of the year of the InvoiceDate value. So for example, if InvoiceDate contains “2/15/2021” I want to get the result “21”. This is what I’ve tried:

RIGHT(YEAR(InvoicedDate),2)

Looks okay to me, but it returns #ERROR. I suspect it’s a typing problem, that is, YEAR() returns a number, but RIGHT() can only be used with a string. But I can’t see how to coerce the number to be a string. I’ve reread the functions reference but can’t find a NUMSTR() function or anything like that. What am I missing?

William

[augmented]

Hi William. Just want to make sure that InvoicedDate is a date field (you put 2/15/2021 in quotes).

If it is a date field, then the error is coming from RIGHT. Try RIGHT(YEAR(InvoicedDate)&"",2). The & concatenation returns everything as a string.

[ScottWorld]

The ideal way to do this would be to use the DATE_FORMAT function like this:

DATETIME_FORMAT(InvoicedDate,'YY')

You can find a list of supported format specifiers here:

Airtable Support

Supported format specifiers for DATETIME_FORMAT

The DATETIME_FORMAT function will allow you to reformat the data from the date-type field into a string of your specifications. This is written in the form "DATETIME_FORMAT(Datetime, 'format s...

[Kamille_Parks]

I was just about to suggest the same. Beat me to it, lol

[WilliamPorter]

Thanks. Yes, InvoiceDate is a date field. I put my example value in quotes just to show that it was a value pulled from a field; sorry about the accidental implication that it might be a string.

I like your way of coercing the type to text by adding & “”. That did the trick nicely. THANKS!

[WilliamPorter]

Heh. I think I’ve asked this question or one very like it before and gotten this good answer. This to me isn’t quite as intuitive as coercing the type to string by adding & “” as @augmented suggested. But your suggestion is one I need to remember, too. Thanks!