Date code serial number in excel

[dadolt]

I need to convert a date into a serial number. The serial number must return the format DDDYYNN where DDD is the day of the year, YY is the year, and NN is the sequential build number. As an example, if 10 units are built on 2/6/2013, the serial numbers would be 0371301, 0371302 ...0371310. Cell A1 would contain the current date (2/6/13) in this example. Column B1 - B10 would be the sequential serial numbers generated by the formula.

Thanks in advance for guidance!

 

[shg]

Maybe ...

=text(a1, a1 - date(year(a1), 1, 0), "000") & text(a1, "yy") & text(b1, "00")

 

[dadolt]

Maybe ...

=text(a1, a1 - date(year(a1), 1, 0), "000") & text(a1, "yy") & text(b1, "00")

Thanks, with your guidance the formula is as follows where B2 is the sequential unit in column B returns 0381301 for a date of 2/7/2013

=TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"yy"))+1),"000")&TEXT(TODAY(),"yy")&TEXT(B2,"00")

<tbody>
</tbody>

 

[shg]

Good job, glad you got it sorted.