International Date Format Conversion and Pivot Grouping by Date

[aiki100]

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!

 

[yky]

Would you post your data using XL2BB?

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[pjmorris]

Some thoughts:

1. If the data looks as you say and is genuinely formated as 'General' then I suspect the data has been given to you in a text format ie it looks like a date, but is actually just a string of characters. Excel doesn't recognise this as a date. A date/time in Excel is stored as a decimal number. The integer bit is the date, the time bit is the decimal (as a fraction of 24).
2. In an empty cell away from your data type '0' (just the number zero). Copy the cell.
3. Click on your data and select 'paste special' (definitely not just paste), and select 'Values' from the top half of the dialogue and 'Add' from the bottom. Select OK.
4. Assuming my guess is correct you will then see instead of '06/01/2014 09:09' you'll have 41645.38125 to see it as a date simply format it however you wish.
5. The pivot table should then behave correctly. What the Paste Special did was add 0 to the value in the cell, but because the value was a string Excel simply thought about it and converted it to a proper date format and added 0. Because the cell is formated as 'General' it shows you the decimal number rather than the normal date.

HTH

 

[yky]

Some thoughts:

1. If the data looks as you say and is genuinely formated as 'General' then I suspect the data has been given to you in a text format ie it looks like a date, but is actually just a string of characters. Excel doesn't recognise this as a date. A date/time in Excel is stored as a decimal number. The integer bit is the date, the time bit is the decimal (as a fraction of 24).
2. In an empty cell away from your data type '0' (just the number zero). Copy the cell.
3. Click on your data and select 'paste special' (definitely not just paste), and select 'Values' from the top half of the dialogue and 'Add' from the bottom. Select OK.
4. Assuming my guess is correct you will then see instead of '06/01/2014 09:09' you'll have 41645.38125 to see it as a date simply format it however you wish.
5. The pivot table should then behave correctly. What the Paste Special did was add 0 to the value in the cell, but because the value was a string Excel simply thought about it and converted it to a proper date format and added 0. Because the cell is formated as 'General' it shows you the decimal number rather than the normal date.

HTH

What I don't understand is if it's text, why would it change in pivot table?

 

[pjmorris]

Simple answer yky is I’m not sure. It depends entirely on how the OP is getting from his raw data to the Pivot and we don’t know that. I agree a straight pivot from text wouldn’t be expected to do that (I don’t think) - might have to have more of a play.

 

[MARK858]

A Pivot table Text will sort alphabetically/1-9 left to right, which is what the Pivot table is doing. it is sorting by the 02 first, then by 2017,2018,2019.

01/02/2019 17:02
01/03/2017 02:07
01/03/2018 06:45
01/03/2019 15:05

format a column as text and type in the data below and do a manual A-Z sort (using the default sort numbers and numbers stored as text separately)

Book1
	A
1	header
2	01/09/2019 10:20
3	01/05/2019 17:30
4	01/01/2020 03:32
5	01/05/2017 21:17
6	01/08/2020 01:21
Sheet1

You will get

Book1
	A
1	header
2	01/01/2020 03:32
3	01/05/2017 21:17
4	01/05/2019 17:30
5	01/08/2020 01:21
6	01/09/2019 10:20
Sheet1

Then enter the first data again and create a Pivot table, you will get the same result

 

[pjmorris]

HI Mark,

That makes sense, though the 'year' component of the OPs data appears to be 2014, whereas the PT is stating 2019 - there must be something else happening too? I've recreated your data/pt and get the same results. When I do the same with the OPs data the dates (unsurprisingly) remain as in the input data.

Think we need clarity from the OP of exactly what his input and output actually are, though I still think hes simply getting text rather than a Date/Time.

Regards

 

[MARK858]

Hi Peter, I see what you mean about the original data. I think as well as knowing exactly what cells contain we are going to need to know the exact steps the OP is taking.

 

[aiki100]

Hello everyone -
I want to thank you all for your input on this problem.

When I first posted, there were no responses for a few days (very unusual, and no problem at all…) so I didn't revisit it until yesterday, when I saw that several of you had responded all at once. Let me thank you for your efforts, and apologize for not getting back to it sooner - I really DO appreciate your taking the time, and for your assistance.

The update as to how I resolved this (as I kept slogging on this issue) is as follows:

1 - Buried in the mass of data was one cell that was not a date - not sure how I missed it, but that fixed one part of the sort issue.

2 - However, the date data was still formatted as General - so I made two new blank columns and used text to column to import the data, space delimited (that stripped out the time to Column #2) and for Column #1, I formatted text to column as date, DMY, like the format for the data I was given.

For whatever reason, this returned the date in MDY as opposed to DMY (original), but it was correct. Not what I expected, but no matter, as at least it is now in date mode, formatted as Date, sortable, and more importantly, a Pivot table will group by date (Years, Quarters, etc).

Thanks again for all your input - I appreciate it very much, and hope you will all have a fine week, wherever you are.

 

[pjmorris]

Hi aiki, I suspect you've not looked closely at what I posted in my first contribution - it uses Paste Special to add 0 to the dates which forces Excel to convert them if it can to a date. Its much easier than splitting the text string etc. With regard to time in the Pivot, you simply ignore that level of detail.

An alternative, especially if you need to import data regularly is to use PowerQuery. With PQ, should you have a rogue entry it will return an Error, but not confuse the entire column - you can either set the error to null or delete the row, or do something even clever.

HTH