Forum Discussion

Jan de Roos's avatar
Jan de Roos
Copper Contributor
May 20, 2017

How can i convert a value to date format?

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

Macros & VBA
Need Help
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    May 20, 2017

    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

  • Michael O'Donnell's avatar
    Michael O'Donnell
    Copper Contributor
    May 20, 2017

    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

Resources