Converting to right DATE format

[loonia]

Hi everyone!

I have a column containing a set of numbers looking like
this:

20030304 (text format)

I wish to convert the entire column to look like this:

04/03/2003 - dd/mm/yyyy

I tried doing so by using a custom made dd/mm/yyyy format
but it's unable to do so because there are no "/" in the
original, I think?

Any help would be most appreciated!

Monika

 

[Don Guillett]

this should do it.

=RIGHT(I5,2)&"/"&MID(I5,5,2)&"/"&LEFT(I5,4)

 

[Debra Dalgleish]

To convert the text to dates, you can use Text to Columns:

1. Select the column with dates
2. Choose Data>Text to Columns
3. Click Next, click Next
4. In step 3, choose YMD from the Date dropdown.
5. Click Finish.

Then, choose Format>Cells, and choose a date format.

 

[loonia]

Hi Don,

You're a legend!

It worked!

Thanks so much.

Monika