Results 1 to 7 of 7

Thread: cannot change date format inside a table

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default cannot change date format inside a table

    After creating a sheet of data and running it thru power query/transpose, copying the output to another sheet, sheet still has the data in a table format, and then trying to change the date format it will not work. Using the two methods below, neither one changes the date format.

    But if I enter data into a spreadsheet and try using the following code on the manually entered data the format id=s changed. I have also tried to click on cell ‘C3’ and change the format by using format cell and choosing ‘mmm-dd’ as an option with no change.


    It seems that after running a sheet thru powerquery and trying to reformat the date either thru VBA or right click on cell and change format nothing changes. Can someone help me understand what I have to change.

    PowerQuery code below:

    let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Type = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Time", type time}, {"Courts", Int64.Type}, {"Teams", type text}}),
    Pivot = Table.Pivot(Table.TransformColumnTypes(Type, {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(Type, {{"Date", type text}}, "en-US")[Date]), "Date", "Teams")
    in
    Pivot

    My 2 ways of trying to change date format using VBA

    Way 1:
    Change date cells from text to date and format them
    Set rng = Sheets("Template").Range(Cells(3, 3), Cells(3, LastCol))
    For Each Cell In rng
    Cell.Number = "MMM-DD"
    Next Cell

    Way2:
    With Sheets("Template")
    .Range("c3:e3").Value = CDate(.Value)
    .Range("c3:e3").NumberFormat = "mmm-dd"

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: cannot change date format inside a table

    Try this

    Code:
      Dim Rng As Range, cell As Range
      With Sheets("Template")
        Set Rng = .Range("c3:e3")
        For Each cell In Rng
          cell.Value = CDate(cell.Value)
          cell.NumberFormat = "mmm-dd"
        Next
      End With

    If the data in the cells are dates then you only need this:
    Code:
      Sheets("Template").Range("c3:e3").NumberFormat = "mmm-dd"
    Regards Dante Amor

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cannot change date format inside a table

    Ran your code and experienced strange results. The dates are actually in c3 to I3 so this gave a good test. The code changed the format to mmm-dd. This I know because when I right click on cell c3 and select format cells the type shows as mmm-dd and right clicking on cells F3 thru I3 the format shows general. Which tells me that all the cells were set as general coming out of ‘power query’. All cells C3 – I3 still have data in mm/dd/yyyy format even though the code change the format to mmm-dd.

    Some more tests.

    I can right click on any of the cells and change the format’ however, the date still shows in its original format of mm-dd-yyyy. If I make an additional sheet in the workbook and copy and paste special (values only) into the new sheet and change the format the days do not change.

  4. #4
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cannot change date format inside a table

    I did some additional testing and found this:

    Copied data below original data and added another set of code to process the additional data. When I copied the data to row 13 I did a paste special-values only and the code worked when running with row 13. The difference that I can see is that the data in row 3 is inside a table and the data in row 13 is not in a table. Clicking on row 13 displays ‘table design’ and ‘query’ tabs in the top level navigation Bar

    I hope this provides you with useful information

    With Sheets("Template")
    Set Rng = .Range("c3:e3")
    For Each cell In Rng
    cell.Value = CDate(cell.Value)
    cell.NumberFormat = "mmm-dd"
    Next
    End With

    With Sheets("Template")
    Set Rng = .Range("c13:e13")
    For Each cell In Rng
    cell.Value = CDate(cell.Value)
    cell.NumberFormat = "mmm-dd"
    Next
    End With

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: cannot change date format inside a table

    The headings in a table are stored as text and you cannot change the format.
    As you can see in the following example.





    If you want May-15 in the header you must change it manually, or use code to change it from May-15 to May-15. For example:


    Code:
    Sub test()
      Dim rng As Range, cell As Range, newcell As Variant
      With Sheets("Template")
        Set rng = .Range("c3:e3")
        For Each cell In rng
          newcell = Split(cell, "-")
          cell.Value = newcell(0) & "-" & newcell(1)
        Next
      End With
    End Sub
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Sep 2008
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: cannot change date format inside a table

    Thank You !!!!!!! Dante

    Sorry for the delay. I was in the Fl. Keys kicking back for a while. Your post is spot on. I finally got it after your great patience and awareness of my problem. The solutions seem so simple when someone else does them for you.

    TY soooo much again

  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,354
    Post Thanks / Like
    Mentioned
    73 Post(s)
    Tagged
    14 Thread(s)

    Default Re: cannot change date format inside a table

    Im glad to help you, thanks for the feedback.
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •