Forum Discussion

brendasc's avatar
brendasc
Copper Contributor
May 03, 2017

Converting to Date Format in Excel

I have a lengthly spreadsheet that lists a milestone date as follows: 

201708
201709
201708
201711
201712
201709

 

I need to convert these to date format (August 2017 or 08/2017, September 2017 or 09/2017 and so on as an example) so I can create a timeline slicer in my pivot table. 

 

How do I do this? 

excel

6 Replies

  • Logaraj Sekar's avatar
    Logaraj Sekar
    Iron Contributor
    May 05, 2017
    =DATE(LEFT(A1,4),RIGHT(A1,2),1)

    Select that column -> Format Cells -> Number Tab -> Custom -> set "mm/yyyy" or "mmmm yyyy"

    • brendasc's avatar
      brendasc
      Copper Contributor
      May 05, 2017
      Selected the column and Entered the formula you indicated =DATE(LEFT(A1,4),RIGHT(A1,2),1), but got #VALUE! in the column header row and the rest of the numbers in the column didn't change. Then I tried the Format Cells path you mention and got mm/yyyy in all of the cells in the column. Took out the "" before and after the custom set and my numbers changed to dates, but they looked like this:
      04/2452
      04/2452
      04/2452

      (these dates were supposed to be corresponding to this:
      201708
      201709
      201708

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    May 03, 2017

    Hi,

     

    Afraid that's only with helper column which you may format as date. If you dates are kept as numbers you may transfer them to dates for example like this

     

    =DATE(A1/100,MOD(A1,100),1)

    If that's text the approach is simular, just another formula.

    • brendasc's avatar
      brendasc
      Copper Contributor
      May 05, 2017

      Thanks for your response....but didn't work. I got this as a return value: #VALUE!

       

Resources