Converting Day Numbers to Dates...

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

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
 
try this formula or a macro to do it for you.
=DATEVALUE(12&"/"&31&"/"&YEAR(TODAY()))-365+A1
 
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.
Click to expand...

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
 
Ciao Franz - this works perfectly!

Amanda

Franz Verga said:
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
Click to expand...
 
shorter than my offering but it also works without the value, even if text
=DATE(YEAR(TODAY()),1,A1)
 
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)
Click to expand...
Yes, I didn't think that also the DATE function makes the conversion from
text to value..
 
Back
Top