date formating

[george.m]

hello all,

i have a file that i get from a database that has multiple date fields and each date is in a different format for example one format is

20180601 which translates to 01.06.2018
and another format is
27/12/20170 with an extra "0" at the end of the year

is there a formula or something that can change those into DD/MM/YYYY format ?

thanks in advance

[Pete_UK]

It would help if you attached a sample Excel workbook, so we can see the range of formats that you encounter..

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Please note that the Paperclip icon (Attachments button) does not work on this forum, so don't try to use that.

Hope this helps.

Pete

[TMS]

Try:

Please Login or Register  to view this content.

copied down

[samba_ravi]

OR CAN TRY
=IF(LEN(A1)=8,TEXT(A1,"####\/##\/##"),LEFT(A1,10))+0
Change the cell format to date
OR
=TEXT(IF(LEN(A1)=8,TEXT(A1,"####\/##\/##"),LEFT(A1,10)),"DD/MM/YYYYY")