cannot change date format inside a table

[breilly00]

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"

 

[DanteAmor]

Try this

  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:

  Sheets("Template").Range("c3:e3").NumberFormat = "mmm-dd"

 

[breilly00]

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.

 

[breilly00]

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

 

[DanteAmor]

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:

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

 

[breilly00]

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

 

[DanteAmor]

Im glad to help you, thanks for the feedback.