Skip to Content

Changing date format for HANA datasource in Analysis for Office

Hi,

Our configuration is as follows:

  • HANA One on AWS
  • Analysis for Office 2.2.1.53549
  • BI 4.1 sp6
  • SAP HANA http OLAP provider

I have seen a few other posts on this topic, but there doesn't seem to have been a definitive answer.

The dates are coming back in a dd.mm.yyyy format for all users. The required format for US based users is mm.dd.yyyy.

The pc regional settings 'English (United Kingdom)' versus 'English (United States)' has no effect.

There is no BW in this particular config, so SU01 is not applicable.

Does anyone know if there is a setting (by user or group) in the BIP or in HANA studio that will achieve this?

Thanks


Tim

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Mar 15, 2016 at 02:02 PM

    Tim:

    Could you check the preferences in the BI launchpad as Karol describes in his response here:

    Add a comment
    10|10000 characters needed characters exceeded

    • Hi Jim,

      I was unable to log a support call with SAP as we are using HANA One on AWS.

      However when we actually deployed this to the US based users, the date formats came through correctly i.e. in a mm.dd.yyyy format. Clearly something in my testing around changing regional setting in Windows did not reflect reality!

      What I had already done as a work around was write a VBA function to toggle the date formats. I just added this to a button in the workbook. In the end it wasn't required. I've copied the code below should that be useful.

      Regards

      Tim

      Sub toggleDateFormat()

      '31/12/2016 --> 12.31.2106

      Dim iRow As Long

      Dim iRows As Long

      Dim iColumn As Long

      Dim iColumns As Long

      Dim colLetter As String

      Dim tDateStr As String

      iRows = ActiveSheet.UsedRange.Rows.Count

      iColumns = ActiveSheet.UsedRange.Columns.Count

      For iRow = 1 To iRows

      For iColumn = 1 To iColumns

      colLetter = getColLetter(iColumn)

      If (Mid(Range(colLetter & iRow), 3, 1)) = "/" Then

      If (Mid(Range(colLetter & iRow), 6, 1)) = "/" Then

      If IsNumeric(Left(Range(colLetter & iRow), 1)) Then

      'assume cell is a date and switch format

      tDateStr = Range(colLetter & iRow).Value

      tDateStr = Mid(tDateStr, 4, 2) & "." & Left(tDateStr, 2) & "." & Right(tDateStr, 4)

      Range(colLetter & iRow) = tDateStr

      End If

      End If

      End If

      Next iColumn

      Next iRow

      End Sub

      Function getColLetter(lngCol As Long) As String

      Dim vArr

      vArr = Split(Cells(1, lngCol).Address(True, False), "$")

      getColLetter = vArr(0)

      End Function

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.