Sequential Project Numbers, number needs to start over at the beginning of year

[pwrlftr198]

I am new to this forum and am hoping someone can help!

I am trying to create a project number checkout logbook. Here is the format of the project number: CS.14.030, where 'CS' is the project category family, '14' is the year, and '030' is the next unused project number. The date is entered by the user and the next unused project number is populated in a hidden column.

I need the project number to start at 001 when a new year begins. So for 2015 (for example), it would be CS.15-001 and count up from there until a date in the next year is input. As you can see in the attached file, if i have a 2014 date followed by a 2015 date, it uses the next available number from 2014 instead of starting over at 001.

I would like to just have one file from now on that could include all years, but I'm not sure if that's possible.

Thanks in advance for your help!

 

[Nebu]

Try using this formula in columnB =IF(YEAR(A2)=YEAR(A3),B2+1,1) and the this formula ="CS." & TEXT(A2,"YY") & "-"& "0" & B2 in coulmn D as u have already been using.

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[pwrlftr198]

Try using this formula in columnB =IF(YEAR(A2)=YEAR(A3),B2+1,1) and the this formula ="CS." & TEXT(A2,"YY") & "-"& "0" & B2 in coulmn D as u have already been using.

This worked great! However, I forgot to mention that the number after CS.14-xxx needs to always be a 3 digit number. Is there a way to set that number up so it is always a 3 digit number?

 

[Somendra Misra]

Hi,

See the file.

Regards,

 

[Nebu]

Try This formula =IF(OR(YEAR(A2)=YEAR(A3),B2<=999),(B2+1),1), not tested
extensively, try it and let me know....