cannot change date format inside a table

breilly00

Board Regular
Joined
Sep 15, 2008
Messages
53
Office Version
  1. 365
Platform
  1. Windows
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"
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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"
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
The headings in a table are stored as text and you cannot change the format.
As you can see in the following example.


ad6e109e1d36538ea34a68607f8fa739.jpg



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
 
Upvote 0
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
 
Upvote 0
Im glad to help you, thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top