Date to Days of the year

M

Mike Smith NC

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.
 
R

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.
Click to expand...

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
 
M

Mike Smith NC

Thanks very much. That solved the problem.

Ron Rosenfeld said:
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
Click to expand...
 
Top