Excel online. How it works with dates in text format

Using DATEVALUE isn’t a good idea if you publish workbook on SharePoint. You have to be sure that locale at your computer is the same as on SharePoint site, where you publish workbook.

090815_0608_Excelonline1.png

In this example date presented as text. SAP ByDesign always shows dates as text in reports. Text depends on user parameters set in ByD.

It can be DD.MM.YYYY or MM/DD/YYYY, or something else. Unfortunately, from report side we cannot get info about set date format. In our region we agreed that default date format that users should use is DD.MM.YYYY.

Return to DATEVALUE. It is a function that determine date from text string. Reasonable question – what is ‘03.04.2015’ (or ‘04.03.2015’). If you don’t know what date format was used DD.MM.YYYY or MM.DD.YYYY, you cannot answer. But we need answer, therefore Excel uses locale set in “Region and language” parameters.

For example, on your computer date format is DD.MM.YYYY and “28.02.2015” is recognized by DATEVALUE as 28-Feb-2015 – cool.

However, when you upload workbook on SharePoint site where locale set to English (U.S.), DATEVALUE in workbook tries to read date as MM/DD/YYYY, but there is no slash (/) sign in “28.02.2015” – first fail of formula. Even if it would be “28/02/2015”, do we have month “28” in calendar? – second fail.

Instead of using DATEVALUE I would recommend to manually cut string on parts using LEFT, MID, RIGHT functions.

If you are sure that first to characters shows day, month in the middle of string with two characters, year is last 4 characters, then

DATE(RIGHT(A1, 4), MID(A1, 4, 2), LEFT(A1, 2)) – will give you right date. This option works without dependency on locale of computer or SharePoint site.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s