Forum Discussion
Solved
Converting date into letters
I receive orders from my customers off of blanket orders. To differentiate releases off of these blanket orders I tag these orders with a short, intuitive alphanumeric code added to the blanket number. This code is derived from the release date using A as a year code starting with 2023, then A - L for January - December, then the release month and day in four numerals. For example, May 19, 2023 would be expressed as AE0519. I would like to a formula to my Excel customer release form that will automatically assign this code. I am a Mac user.
Any takers?
Jim_T_0228 CHR should have been CHAR and did you replace the "d" with the cell reference? see attached example
5 Replies
if you have Excel 365 I recommend a LAMBDA function:
DateCode := LAMBDA(d, CHAR(65+YEAR(d)-2023) & CHAR(64 + MONTH(d)) & TEXT(d,"mmdd") )
and then you can call from any cell =DateCode(A1)If you don't have Excel 365 or prefer not using a LAMBDA then the formula is just:
=CHAR(65+YEAR(d)-2023) & CHAR(64 + MONTH(d)) & TEXT(d,"mmdd")
where d is the date or reference to the date to be used
Oh-boy... help me unpack THAT one... My version of Excel doesn't have a CHR or LAMBDA function. There is a CHAR function, but when I append everything else you note below (with spaces and ampersands) it gives me a name error.
What am I doing wrong, or rather is there another way you can think of?
Jim_T_0228 CHR should have been CHAR and did you replace the "d" with the cell reference? see attached example
