Convert number string to a date

[dsouza]

Hi,

Can you please give me the calculation I need in order to convert the following into a date that excel can read?

31072016

01082016

01082016

01082016

01082016

01082016 ETC Many thanks, Viv

<tbody>
</tbody><colgroup><col></colgroup>

 

[steve the fish]

Assuming you always have the leading zeroes for single figure days/months then this works

=DATE(RIGHT(A1,4),MID(A1,3,2),LEFT(A1,2))

 

[godsaaint]

In this case, you shouldn't need a formula, just select the cells, and use Text to Columns (In the data tab) and choose date in the column data format.

 

[Tetra201]

Or this:

=--(RIGHT(A1,4)&"-"&MID(A1,3,2)&"-"&LEFT(A1,2))

 

[dsouza]

Thanks guys, most helpful

 

[Tetra201]

You are welcome.

 

[Amanda Wilso]

=TEXT(A1,"0000-00-00")+0
or
=--TEXT(A1,"0000-00-00")

Format the cell containing this formula with an appropriate date format.

 

[Tetra201]

=TEXT(A1,"0000-00-00")+0
or
=--TEXT(A1,"0000-00-00")

Format the cell containing this formula with an appropriate date format.

Are you sure these will work with 31072016?

 

[dsouza]

Sorry to ask a stupid question folks. How do I close this thread now you have so gratiously given me a solution?

 

[63falcondude]

Sorry to ask a stupid question folks. How do I close this thread now you have so gratiously given me a solution?

On this forum there is no way for you to mark a thread as closed or solved. The best thing to do is exactly what you did in post #5.