Generate alphanumeric code based on date in cell

Hi all,

I have been working on a way to figure out if a request (one row) is approved, an alphanumeric code is generated based on the date that request was approved.

It should look like this "CR" + "YEARDATE-1" (so if I approve my first request in april 2017) the number generated should look like this CR201704-1. The last digit should be determined based on the already approved requests.

No I am stuck because the date is lined to the system date and the last digit is lined to the row, but this gives a false image since row 17 mag give "17" in the end whereas I only approved three requests you see.

This is the code I am using now:

=IF($R15="","",IF($R15="Rejected","",IF($R15="Approved","CR"&YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&"-"&TEXT(ROW()-1,"0000")-13)))

First row of the data is "15".

Thank you all for your help on this one! It's really appreciated.

Kind regards,

Bram

Ever consider what happens to the calculated code next month or next year...?

--------------

Can you attach the spreadsheet (Go Advanced>Manage Attachments)? Show only enough data so we can see what you are trying to do. (too big and it won't upload)

One problem I see right away is you are trying to create static numbers but using the TODAY function. Every day your numbers will update to reflect today's date.
I suggest a column where you can enter a static date (Helpful hint, CONTRL + ; will insert today's date as a static value) and then reference that cell in your formula

I can't give more help until I see the workbook

Try this in U15 copied down.
I assume that if rows are empty (because of "Rejected" in R) then you wouldn't want to skip numbers.

=IF($R15="","",IF($R15="Rejected","",IF($R15="Approved","CR"&YEAR(S15)&TEXT(MONTH(S15),"00")&"-"&TEXT(SUMPRODUCT(--($U$14:$U14<>""))+1,"0000"))))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.