International Date Format Conversion and Pivot Grouping by Date

aiki100

Board Regular
Joined
Aug 16, 2016
Messages
102
Greetings all -
I have a date format and pivot grouping issue, please:

I've been given output in the format d/m/y 00:00. I believe this is standard Euro Area/Australian date format. This column, as given to me, is formatted as General.
I have set my windows regional formatting to use Australia , which is where this data is being generated (thus, their formatting). I have NOT changed my actual time and date- I have only changed regional formatting to Australia.
I checked Options in Excel and see no other settings that I think might affect this outcome.

What I have tried:
I tried converting the column as is, format to DATE.
I tried using LEFT to strip out the time, and pasted as values into a clean column (also formatted as DATE).

What happens:
When I attempt to pivot table this data in order to group by years (and other date) I get the following output:
Here is what I start with:
06/01/2014 09:09 (Jan 6, 2014, in Aus/euro date format)
08/01/2014 02:31
09/01/2014 13:39
10/01/2014 01:53

Here is what the pivot does:
01/02/2019 17:02
01/03/2017 02:07
01/03/2018 06:45
01/03/2019 15:05

Can anyone guide me as to what I am doing wrong here, please?
The goal, as I hope is evident, is simply to have the data as I have been given it (in euro format), rendered grouped by date via pivot table and retain its original format.

Any and all thoughts very much appreciated, thank you!
 
Outstanding! Thank you @pjmorris for pointing that out - I will indeed follow up. Thanks again for your insight - it would appear I have some (more) learning, bc there is ALWAYS something new to learn!
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
thanks for the feedback and pleased to help. Enjoy the learning (as we all do!).
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,731
Members
448,294
Latest member
jmjmjmjmjmjm

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