Can we change the date format in Analysis tool

[Former Member]

Hi ,

Presetly we are using HANA as database to build reports in Analysis . The View has the dat format displayed as "MM.DD.YYYY" but in Analysis it shows up as DD.MM.YYYY.Is there a possibility to change the DATE format in Analysis accordingly for different countries?

[Former Member · Accepted solution]

Hello Hema,

As far i know AA doesn't have the settings with respect to DATE format but an indirect way is to go for Calculation option or put a formula in Excel to convert into desired format.

Regards

Ganesh

[petedonald]

The date format displayed for in Analysis for Office is dependent on the user prefernences in SAP Client.

To change this log into SAP Client: More>System>User Profile> User Data
Go to Defaults tab and change the Date Format

This worked for me and I was able to change from dd.mm.yyyy to mm/dd/yyyy

[Former Member]

Hi,

Did anything change in the meantime in regards to this topic?

Thanks for the reply!

Bram.

[Former Member]

Hi Bram,

I cannot talk about HANA specifics, but I would assume it is dependent from your SU01 user settings in the system how time and number formats are displayed in (at least BEX) queries in AO.

Have you tried changing the user settings?

Regards, Martin

[Former Member]

Hi Martin,

Thanks for your reply.

The date indeed works with the SU01 settings, but the problem there is that we're missing the format DD/MM/YYYY, which is the format business requires...

So unless we can add a format there (I did not find a way to do this), we cannot meet the business requirements.

Regards,

Bram.

[Former Member]

Hi Bram,

this sounds like a strange Business Requirement?!

SAP Standard offers: DD.MM.YYYY (German Format) or MM/DD/YYYY (English Format).

What is the benefit of formatting a date in a mixed way?

However, I guess there are only two solution options:

1) Either you can use the "SAPMemberCell" and change it accordingly

2) or you have to use VBA coding and Format only specific cells, as required.

In the past (with WAD 3.5) we used a render class to create "special" number formats... I guess it is the same principle here, just VBA-based.

Regards,

Martin

[Former Member]

Hi Martin,

When the user's date-format is set for example to DD.MM.YYYY in the BW

back-end, this is also the format in which BO Analysis for Excel will

show dates when creating a workbook.

This date-format is not recognized as a date by MS-Excel and

consequently the dates cannot be used in formula's, vlookups etc...

The DD/MM/YYYY format is standardly recognized by Excel, but not available in SAP...

Any ideas?

Regards,

Bram.

[Former Member]

Hi Bram,

I guess, I know where the real Problem is...it's the ' before the Dates, e.g. '02.09.2014.

Here it doesn't matter if it's DD.MM or DD/MM the ' will always prevent Excel to recognize it as a date.

My solution for this is, to use e.g. a separate colum and Excel formula =DATVALUE()

(German: DATWERT)

With that you get a real number out of the date and then you can format it in Excel to the date you want to have. Then formulas will work and the standard Excel filter will recognize and aggregate it in a hierarchical display.

Example:

You can see the difference of left-alignment in C2 and right-alignment in E2.

Regards, Martin

[Former Member]

Hi Martin (& other BI'ers),

I finally got a reply from OSS support, stating 'works as designed' and that I need to submit an idea.

Although I don't give up yet, I already submitted the idea as well.

The idea can be found under Align Date format with the regional settings from Windows, not with the default values from SU01. : ... , feel free to promote it in order to get it more in the spotlight of the SAP development team.

Thanks!

Bram.

[stephen_hobbs2]

We have just encountered this issue today! We have just converted a BEx 3.5 workbook to Analysis. In 3.5 workbook the date is rendered 26/09/2014 but in the Analysis version it is 26.09.2014. Even in the prompt variables they are different between BEx and Analysis.

It is true that Analysis uses your SU01 settings but it appears that BEx 3.5 doesn't.

This is not a huge issue for us but one that has affected at least one workbook.

Bram, I have promoted your idea...

Stephen

[Former Member]

Hi Hema,

Please check your regional settings on the workstations.