KNOWLEDGE BASE

Unable to Convert Week of Year String to Date Format


Published: 16 Oct 2015
Last Modified Date: 25 Sep 2017

Issue

You are unable to convert string date data (for example, in the format "2017 W01") into a date field.

Environment

Tableau Desktop

Resolution

  1. Select Analysis > Create Calculated Field
  2. In the Calculated Field dialog box that opens, do the following, and then click OK:
    1. Name the calculated field. In this example, the calculated field is named "Date".
    2. In the formula field, create a calculation similar to the following:
DATEADD( 'week', INT( RIGHT( [Date], 2 ) ) -1, DATE( "1/1/" + LEFT( [Date], 4 )))

Cause

"Week number" is not a commonly recognized date format.

Additional Information

In the above calculation, the DATE() function converts the string year data into a date format. The DATEADD() function then adds on the number of weeks. By default the DATE() function returns the first week of the year so adding week number "01" shifts all the weeks by one, which is why we subtracted one from week number.

Please note, the above calculation assumes all of the week numbers will be two digits.
 
Did this article resolve the issue?