Time/Date Formats when exporting Data to Excel | Community
Skip to main content
M
m-spencer
New Participant
April 1, 2015
New

Time/Date Formats when exporting Data to Excel

  • April 1, 2015
  • 9 replies
  • 7165 views

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

    R
    RossBossCurtis
    New Participant
    April 15, 2024

    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. 🙂

    stefanies325986
    stefanies325986
    New Participant
    February 12, 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

    _jm_97660
    _jm_97660
    New Participant
    February 12, 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

    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

    stefanies325986
    stefanies325986
    New Participant
    February 12, 2016

    Hi JM,

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

    Source image file 21642 not available

    M
    m-spencerAuthor
    New Participant
    July 31, 2015

    @_JM_

     

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

    _jm_97660
    _jm_97660
    New Participant
    June 5, 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

    stefanies325986
    stefanies325986
    New Participant
    June 2, 2015

    Hi Gary,

    would you mind sharing the exact formula?

    Regards

    Stefanie

    gary_mason
    gary_mason
    New Participant
    June 1, 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.

    stefanies325986
    stefanies325986
    New Participant
    April 21, 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

    Terms & ConditionsAccessibility statement

    Loading footer...