Time/Date Formats when exporting Data to Excel | Community
Skip to main content
m-spencer
Level 2
Apr 1, 2015 11:46 PM UTC
New

Time/Date Formats when exporting Data to Excel

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.

    9 replies

    RossBossCurtis
    New Participant
    Apr 15, 2024 10:44 AM UTC

    How about this as a formula where A2 is March 16, 2024

    And where the formula is:

    =DATE(RIGHT(SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"),4),MONTH(DATEVALUE("1-"&TEXT(MID(SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"),1,FIND("¢",SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"))-1),"mm")&"-1900")),MID(SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"),FIND("¢",SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"))+1,FIND("∞",SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"))-(FIND("¢",SUBSTITUTE(SUBSTITUTE(A2,", ","∞",1)," ","¢"))+1)))

     

    It's not the most elegant of formulas, but it worked for me. 🙂

    Translate
    stefanies325986
    stefanies325986
    Level 4
    Feb 12, 2016 4:27 AM UTC

    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

    Translate
    _jm_97660
    _jm_97660
    Level 3
    Feb 12, 2016 4:25 AM UTC

    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

    Source image file 21644 not available

     

    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

    Translate
    stefanies325986
    stefanies325986
    Level 4
    Feb 12, 2016 3:50 AM UTC

    Hi JM,

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

    Source image file 21642 not available

    Translate
    m-spencerAuthor
    Level 2
    Jul 31, 2015 1:46 AM UTC

    @_JM_

     

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

    Translate
    _jm_97660
    _jm_97660
    Level 3
    Jun 5, 2015 5:40 AM UTC

    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

    Translate
    stefanies325986
    stefanies325986
    Level 4
    Jun 2, 2015 4:16 AM UTC

    Hi Gary,

    would you mind sharing the exact formula?

    Regards

    Stefanie

    Translate
    gary_mason
    gary_mason
    Level 2
    Jun 1, 2015 9:22 AM UTC

    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.

    Translate
    stefanies325986
    stefanies325986
    Level 4
    Apr 21, 2015 1:27 AM UTC

    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

    Translate
    Terms & ConditionsAccessibility statement

    Loading footer...