I need a serial number generated from a couple of sources. One is a "Unit
Number" column and the other is a "Date Column". I need the date to be
converted from mm/dd/yy in one cell to yy***. *** being the day of the year
that the serial number was created. how can I do this? Thanks.

On Thu, 6 Jul 2006 12:45:01 -0700, Mike Smith NC
wrote:

I need a serial number generated from a couple of sources. One is a "Unit
Number" column and the other is a "Date Column". I need the date to be
converted from mm/dd/yy in one cell to yy***. *** being the day of the year
that the serial number was created. how can I do this? Thanks.

Perhaps:

=UnitNumber &TEXT(Date,"yy")&TEXT(Date-DATE(YEAR(Date),1,0),"000")

although if UnitNumber can have leading zeros, you might want to make it TEXT
also:

=TEXT(UnitNumber,"000") &TEXT(Date,"yy")&TEXT(Date-DATE(YEAR(Date),1,0),"000")

Date = date in the date column

DATE = the worksheet function DATE.

Sorry for the confusion.


--ron

Thanks very much. That solved the problem.

"Ron Rosenfeld" wrote:

On Thu, 6 Jul 2006 12:45:01 -0700, Mike Smith NC
wrote:

I need a serial number generated from a couple of sources. One is a "Unit
Number" column and the other is a "Date Column". I need the date to be
converted from mm/dd/yy in one cell to yy***. *** being the day of the year
that the serial number was created. how can I do this? Thanks.

Perhaps:

=UnitNumber &TEXT(Date,"yy")&TEXT(Date-DATE(YEAR(Date),1,0),"000")

although if UnitNumber can have leading zeros, you might want to make it TEXT
also:

=TEXT(UnitNumber,"000") &TEXT(Date,"yy")&TEXT(Date-DATE(YEAR(Date),1,0),"000")

Date = date in the date column

DATE = the worksheet function DATE.

Sorry for the confusion.


--ron

On Thu, 6 Jul 2006 13:16:03 -0700, Mike Smith NC
wrote:

Thanks very much. That solved the problem.

You're welcome. Thanks for the feedback.

--ron