Date Format VBA code

HikingGiraffe

New Member
Joined
Jul 22, 2021
Messages
8
Office Version
  1. 365
Platform
  1. Windows
I have searched the internet tirelessly for an answer to this question that I desperately need more understanding on.
What I am trying to do is create a macro that will take raw data on one sheet and put it into a pivot table on a new sheet - pretty standard procedure right?

The issue is that I cannot, for the life of me, figure out how to make the dates in column C read in date format instead of default text or general format. On the pivot table, it is asking the user to sort A-Z or Z-A which sorts by the month / numerical order instead of newest to oldest which is what I am looking to do.

I am well aware of all the manual fixes to this, but the intention is for this sheet to be opened, data to be imported, and the table to be ready to analyze.
So with that being said, this is the section of the code I am working with:

Private Sub Workbook_Open()
Dim FileToOpen As Variant
Dim OpenBook As Workbook
FileToOpen = Application.GetOpenFilename(Title:="Browse for your File & Import Range", FileFilter:="Excel Files (*.xls*),*xls*")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
OpenBook.Worksheets("Aggregate View").Range("A1:Y300").Copy
ThisWorkbook.Worksheets("AggregateView").Range("A1").PasteSpecial xlPasteValues
OpenBook.Close False
End If
Dim datrange As Range
Set datrange = Range("C9:C500")
datrange.NumberFormat = "MM/dd/yyyy"
'The following line will run Macro2
Call WageSummaryPivotTableVersion1_1
End Sub

I am not getting any error codes, and it is changing the selected cells but it's changing them to a custom format that isnt properly reflecting date format in the pivot table.

Please feel free to massacre my code, I am brand new at VBA and would love to hear any and all suggestions for improvement.

Thank you!
 
It's fixed!

I took a couple days to really look into your suggestions and you're right, it was a data issue not a macro issue.
I recorded a new pivot table macro that included text to columns > fixed width > Date format MDY.
I had been messing around with text to columns in the beginning of this project but always chose delimited instead of fixed width. I never would have tried going back to working with my aggregate data if you hadn't helped me run through all my options.

Through my extensive internet search for an answer, I know this is a common issue with pivot tables, so I really hope this thread helps someone else in the future.

It is such a relief to actually see my dates in order after weeks of trying to fix the issue. I cant thank you enough!!

p.s. not that it matters now, but I am in Central Standard Time :)
 
Upvote 0

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Forum statistics

Threads
1,212,929
Messages
6,110,743
Members
448,295
Latest member
Uzair Tahir Khan

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