How to convert the dates from the YY:DD forma to MM/dd/YYYY format

[Guest]

Hello,

I have a column with dates in the format YYD [here DD stands for day of
the year]. Is there a way to have a column that represents the same dates in
the normal MM/dd/YYYY format?
[MM=month#, dd=day# (day of the month), YYYY=year]

Thank you!

 

[JE McGimpsey]

One way:

Assuming that 2-digit years <=30 are 21st century and years > 30 are
20th century:

=DATE(--(IF(--LEFT(A1,2)<=30,"20","19")&LEFT(A1,2)),1,--MID(A1,4,3))

Note: I assumed that DD could also be DDD for day of the year over 99.

 

[ScottO]

So 05:32 = 1 Feb 2005?
If so, then assuming that all dates are after 1 Jan 2000, and all YY
are 2 digits (ie 2005 is always 05 and never just 5), and your date
to convert is in A1, then this should work ...

=DATE(VALUE(LEFT(A1,2))+2000,1,1)+MID(A1,4,3)-1

Rgds,
ScottO

| Hello,
|
| I have a column with dates in the format YYD [here DD stands for
day of
| the year]. Is there a way to have a column that represents the
same dates in
| the normal MM/dd/YYYY format?
| [MM=month#, dd=day# (day of the month), YYYY=year]
|
| Thank you!

 

[Guest]

Thank you very much for your reply! However, I am still experiencing
problems...

I noticed that my date column YYD has a SPACE in front of YY. Once I
remove the space, the formula below works perfectly. How can I remove this
space?

Thank you!!

One way:

Assuming that 2-digit years <=30 are 21st century and years > 30 are
20th century:

=DATE(--(IF(--LEFT(A1,2)<=30,"20","19")&LEFT(A1,2)),1,--MID(A1,4,3))

Note: I assumed that DD could also be DDD for day of the year over 99.

Hello,

I have a column with dates in the format YYD [here DD stands for day of
the year]. Is there a way to have a column that represents the same dates in
the normal MM/dd/YYYY format?
[MM=month#, dd=day# (day of the month), YYYY=year]

Thank you!

 

[Roger Govier]

Hi Sam

You could just modify JE's formula to remove the spaces
=DATE(--(IF(--LEFT(TRIM(A1),2)<=30,"20","19")&LEFT(TRIM(A1),2)),1,--MID(TRIM(A1),4,3))

--
Regards
Roger Govier

Thank you very much for your reply! However, I am still experiencing
problems...

I noticed that my date column YYD has a SPACE in front of YY. Once I
remove the space, the formula below works perfectly. How can I remove
this
space?

Thank you!!

One way:

Assuming that 2-digit years <=30 are 21st century and years > 30 are
20th century:

=DATE(--(IF(--LEFT(A1,2)<=30,"20","19")&LEFT(A1,2)),1,--MID(A1,4,3))

Note: I assumed that DD could also be DDD for day of the year over 99.

Hello,

I have a column with dates in the format YYD [here DD stands for day
of
the year]. Is there a way to have a column that represents the same
dates in
the normal MM/dd/YYYY format?
[MM=month#, dd=day# (day of the month), YYYY=year]

Thank you!