How can i convert a value to date format?

[Jan de Roos]

Hi,

I have a CSV imported file. In this file the date value is captured as 20170424 (representing 24th of April 2017). Excel date format is unable to use this as a date value. Can someone advise on how to change 20170424 to 24-04-2017 by using a formulae or macro?

Thanks and regards,

Jan

[#2]

Hi Jan,

Try this formula =DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2)); where C2 contains the text string 20170424.  The formula should give you a numeric value.  Format that numeric value as date.

Hope it works,

Thanks,

Mike

[#3]

No macros!

No formulas!

Go to Data > Text to Columns, then go to step 3, select the following, and then press Finish.

After you finish this process, you can change the date format and make it as you want.

To learn more, please follow this link:

Format a date the way you want

[#4]

Thanks Mike, it worked!

Regards, Jan