Date to Days of the year
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. |
Date to Days of the year
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 |
Date to Days of the year
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 |
Date to Days of the year
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 |
All times are GMT +1. The time now is 10:22 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com