Converting Day Numbers to Dates...

[Guest]

I'm using XL2003. For manufacturing purposes, products are stamped with a
serial number that includes the day of manufacture, represented by the day
number, i.e. January 1st = 001, 1st February = 032 and so on. Each new year
begins again at 001.
Is there a quick way to convert this 3-digit day number to an actual date?
Thanks in advance,
Amanda

 

[Franz Verga]

Nel post *Birmangirl* ha scritto:

I'm using XL2003. For manufacturing purposes, products are stamped
with a serial number that includes the day of manufacture,
represented by the day number, i.e. January 1st = 001, 1st February =
032 and so on. Each new year begins again at 001.
Is there a quick way to convert this 3-digit day number to an actual
date? Thanks in advance,
Amanda

Hi Amanda,

=DATE(YEAR(TODAY()),1,VALUE(D14))

In D14 you have your 3 digits day.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Don Guillett]

try this formula or a macro to do it for you.
=DATEVALUE(12&"/"&31&"/"&YEAR(TODAY()))-365+A1

 

[Franz Verga]

Nel post *Franz Verga* ha scritto:

Nel post *Birmangirl* ha scritto:


Hi Amanda,

=DATE(YEAR(TODAY()),1,VALUE(D14))

In D14 you have your 3 digits day.

Because if you open next year the file with 2006 data, the dates would
change, you can change the above formula n this way:


=DATE(D12,1,VALUE(D14))

where in D12 you have to type 2006.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Guest]

Ciao Franz - this works perfectly!

Amanda

Nel post *Birmangirl* ha scritto:


Hi Amanda,

=DATE(YEAR(TODAY()),1,VALUE(D14))

In D14 you have your 3 digits day.

--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

 

[Don Guillett]

shorter than my offering but it also works without the value, even if text
=DATE(YEAR(TODAY()),1,A1)

 

[Franz Verga]

Nel post *Don Guillett* ha scritto:

shorter than my offering but it also works without the value, even if
text =DATE(YEAR(TODAY()),1,A1)

Yes, I didn't think that also the DATE function makes the conversion from
text to value..