Date to Days of the year

[Guest]

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.

 

[Ron Rosenfeld]

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

 

[Guest]

Thanks very much. That solved the problem.

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

 

[Ron Rosenfeld]

Thanks very much. That solved the problem.

You're welcome. Thanks for the feedback.

--ron