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
<(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
<(E-Mail Removed)> wrote:

>Thanks very much. That solved the problem.

You're welcome. Thanks for the feedback.

--ron