0

Export date member column to excel as date and not Text

Is there a way to export a report to excel with date column formatted as date and not text?

1 reply

null
    • Senior Director of Product
    • Ian_Macdonald
    • 1 yr ago
    • Reported - view

    Hi Farhat,

    By "date column formatted as date and not text" I presume you mean in Excel's internal representation of dates.

    Excel stores dates as the number of days since 1900-01-01, with that date represented as 1 and all subsequent dates incremented by 1.

    We don't natively export dates in this way to Excel, but you can easily create an Excel number for your dates using the DateDiff() function:

    Because of how the Excel dates get their number (1900-01-01 = 1), we have to adjust the comparison date back a couple of days to generate the right value for the number of days. So CreateDate(1899,12,30) creates a date of that value which is then compared to the CurrentMember Caption of my Date hierarchy, "dy" defining that I want the difference in days. Note that the output is directed to the Measures dimension.

    Putting Date and my new Measure, Excel date into Discover gives me:

    Exporting to Excel and setting the Excel date column to Long Date gives me:

    Hope that helps.

    Ian

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 1Replies
  • 25Views
  • 2 Following