Converting Number String to a Date

[jmurg]

I have a need to change a number string into a date. The number will always be formated as such: DDD-YY-NNNNNN

DDD represents the counted number of the day of the year
ex: 025 is January 25th and 365 is December 31st.

YY is the numerical year
ex: 09 is 2009 and 10 is 2010

NNNNNN are extra characters that do not apply for date purposes.

Given the above number, I'd like to create a formula that outputs the date as this format: MM/DD/YYYY ex: 01/25/2010

Does anyone have a suggestion on how to accomplish this?

 

[barry houdini]

Try like this where data is in A2

=DATE(MID(A2,5,2)+100,1,LEFT(A2,3))

assumes all dates are this century

 

[jmurg]

very nicely done! you remind me of the circuit city commercials, "I guess you're just what I needed!" (singing the jingle)
You rock - I appreciate the prompt and accurate response!

 

[jmurg]

Can we add two small changes to this formula?

1. Can we make it so the format for the date output is MM/YY/YYYY? ... the formula output M/YY/YYYY when I used 025 for example.
2. Can we make it so if A2 has no value in it, no error appears in the cell? When nothing is in cell A2, I'm getting #VALUE in the cell where my new formula is located.

 

[barry houdini]

I'm not sure I understand what you want for point 1. The formula returns a date so you can use

Format > Cells > Number > date

to format the output any way you like

for the second one you can just add an IF like this

=IF(A2="","",DATE(MID(A2,5,2)+100,1,LEFT(A2,3)))