Convert 20512 To A Date Format

[eddiegnz1]

I have a column of dates but they lack any format and excel converts it to the wrong date. For example, I need to convert 20512 to 02/05/2012.

Excel takes 30510 and converts it to 07/13/1983 but it should be 03/05/2010. None have a leading zero.

In all cases the last two digits are the year, the next two digits from the right are the day and the rest of the digits are the month.

More examples

11110 = 01/11/2010 (this one has only five digits)

121109 = 12/11/2009 (this one has six digits)

40909 = 04/09/2009 (this one has five digits only)

Kind Thanks,
Eddie

 

[martindwilson]

try
=DATEVALUE(TEXT(A1,"00-00-00"))

 

[AlphaFrog]

Format column B as Date

*	A	B
1	11110	1/11/2010
2	121109	12/11/2009
3	40909	4/9/2009

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:92px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TEXT(A1,"00-00-00")*1 B2 =TEXT(A2,"00-00-00")*1 B3 =TEXT(A3,"00-00-00")*1 <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[xladept]

Hi eddiegnz1,

Try this:

=IF(LEN(E8)<6,"0"&MID(E8,1,LEN(E8)-4)&"/"&MID(E8,LEN(E8)-3,2)&"/20"&RIGHT(E8,2),MID(E8,1,2)&"/"&MID(E8,LEN(E8)-3,2)&"/20"&RIGHT(E8,2))

 

[T. Valko]

I have a column of dates but they lack any format and excel converts it to the wrong date. For example, I need to convert 20512 to 02/05/2012.

Excel takes 30510 and converts it to 07/13/1983 but it should be 03/05/2010. None have a leading zero.

In all cases the last two digits are the year, the next two digits from the right are the day and the rest of the digits are the month.

More examples

11110 = 01/11/2010 (this one has only five digits)

121109 = 12/11/2009 (this one has six digits)

40909 = 04/09/2009 (this one has five digits only)

Kind Thanks,
Eddie

Try this...

Select the range of cells in question
Goto Data>Text to Columns
Click Next twice
In Step 3 of the userform, under Column Data Format, select Date and from the drop down select MDY
Click Finish

 

[eddiegnz1]

awesome, y'all are amazing. I much appreciate all of y'alls help. Kind Thanks

 

[T. Valko]

awesome, y'all are amazing. I much appreciate all of y'alls help. Kind Thanks

You're welcome. We appreciate the feedback!