Time/Date Formats when exporting Data to Excel

Avatar

Avatar

m-spencer

Avatar

m-spencer

m-spencer

01-04-2015

When saving reports from both Adobe Analytics and more importantly, Adobe Ad-Hoc Analytics would love to see that any date/time dimention on a report is formatted correctly on the Excel Spreadsheet to allow for easily sorting/ordering etc.

 

For example, when running a Ad-Hoc report broken down by various months over a long time period, I want to be able to display this in date order (Which isn't possible in Ad-hoc to sort by the dimention at present). As these aren't date formatted for Excel, I have to manually move each row of this report to show the months in the correct order which is a time consuming process.

8 Comments (8 New)
8 Comments

Avatar

Avatar

stefanies325986

Avatar

stefanies325986

stefanies325986

20-04-2015

Hi,

I agree with you, this is annoying and kind of silly to have a date as text format. I created a formula workaround with vlookup...

Clientcare says this is a feature, not a bug!

Thanks for posting the idea.

Regards

Stefanie

Avatar

Avatar

gary_mason

Avatar

gary_mason

gary_mason

01-06-2015

Hi,

 

I have seen this problem too and could only fix it by inserting a column in Excel to run the date through an IF/VALUE/TEXT style formula to change the formatting.

Avatar

Avatar

stefanies325986

Avatar

stefanies325986

stefanies325986

01-06-2015

Hi Gary,

would you mind sharing the exact formula?

Regards

Stefanie

Avatar

Avatar

_jm_97660

Avatar

_jm_97660

_jm_97660

04-06-2015

Hi @Stef @Gary @Michael_Spencer

 

You should be able to sort by date/month if you change the 'Font and Locale' settings under Tools > Settings in AdHoc Analysis to English (International).

 

FontLocale.png

 

ExcelFromAdHoc.png

Avatar

Avatar

m-spencer

Avatar

m-spencer

m-spencer

30-07-2015

@_JM_

 

I did what you suggested above however on export it was still formatted as a text field.

Avatar

Avatar

stefanies325986

Avatar

stefanies325986

stefanies325986

11-02-2016

Hi JM,

for me it does not work either... Maybe you are using another version of Excel?

Avatar

Avatar

_jm_97660

Avatar

_jm_97660

_jm_97660

11-02-2016

Hi Stef,

 

Sorry if it is not working. For me and my colleagues it worked by just changing the locale.

I am using Excel 2010 (Windows)

 

Attached is the screenshot from Discover for both locales. The only thing I did was changing the locale and restarting Discover. As you can see here, the date format is automatically changed to the Excel friendly dd-mmm-yyyy format

 

Please note that if you are using International locale then you might not see the segments,metrics,dimensions etc due to a recent bug in Java/Discover

More details here: Known issue with Ad Hoc Analysis and Java 8 Update 71

Avatar

Avatar

stefanies325986

Avatar

stefanies325986

stefanies325986

11-02-2016

Hi,

by the way: when exporting from data warehouse, it is also not working for me.

There is simply text, i.e. "December 2015".

BR