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
"Sam" wrote in message
...
Thank you very much for your reply! However, I am still experiencing
problems...

I noticed that my date column YY:DD 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!!

"JE McGimpsey" wrote:

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.

In article ,
Sam wrote:

Hello,

I have a column with dates in the format YY:DD [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!