Creating a Unique ID in a specific format without duplication

[Fin22]

Hi

We're trying to create a unique number id in the format of NCYYYY-###.

Our current formula, below, gives us a unique ID.
=CONCATENATE("NC",[@YEAR],"-",TEXT(COUNTIFS([YEAR], [@YEAR], [DATE], "<="&[@DATE]), "00"))

BUT

We get duplications of our unique ID if we have more than one entry for a given day (see highlighted yellow rows).

How do we keep this format and also have the possibility of multiple entries on any given day??

Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

=CONCATENATE("NC",[@YEAR],"-",TEXT(COUNTIFS([YEAR], [@YEAR]), "00"))