4 ways to prevent date conversion problems when building date strings

Recently I was working on a project which had month and year columns but no actual date field in Tableau.

Simple enough, I created a calculated field which added a day number (01) to the front of the string.

picture 1 blog.png

I then changed the data type to a date and we were away, we now have the ability to use Tableaus date/time manipulation functions.

Little did I know that when I sent my workbook over to the states, because of the way in which I have built my date field, and because of the way Americans read their dates (mm/dd/yyyy) my pretty YTD trend graph was now broken. It now showed just a single point because all of my month values were, as defined by my calculated field “01”.

(damn those pesky Americans and their unconventional metrics).

Picture 2 blog.png

Okay, you could of course just get your users to change their default local settings to the UK region, but this could have further impact on workbooks that rely on the US date settings, and perhaps more importantly what if your audience doesn’t know about the change and as a result miss-interpret the dates.

Well in this blog I will outline four ways in which we can prevent this problem.

  1. Create your dates in the backend (probably not the most helpful tip, but as a result Tableau will automatically now how to read them dependant on the users settings).
  2. Build your calculated field as [Year]+”/”+[Month]+”/01”, this format (yyyy/mm/dd) is independent of regional settings.
  3. Use the above structure (yyyy,mm,dd) with the makedate() function, so in our example Makedate([Year],[Month],1)
  4. Build an epic calculation (or steal the one here), which generates the unique number for the underlying date which can then be converted to a date by either wrapping date() around the formula, or changing the field type to date.Every date since 01/01/1900 is given a unique identifier based on the number of days from this start date (this is called the 1900 system). 01/01/1900 is assigned the value 1 (given that it is the 1st day), 02/01/1900 being 2, etc.As a result it is possible to create a formula that converts month/year values into a full date.

    //Calculate the number of non-leap years between your date and 1900, then multiply this by 365

    ((([Year]-1900)-INT((([Year]-1900)/4)+IIF((([Year]-1900)/4)<>INT((([Year]-1900)/4)),1,0)))*365)

    //Calculate the number of leap years between your date and 1900, then multiply this by 366

    +(((INT(([Year]-1900)/4))+IIF((([Year]-1900)/4)<>INT((([Year]-1900)/4)),1,0))*366)

    //Calculate whether the year of the date is a leap year

    +IIF((([Year]-1900)/4)<>INT((([Year]-1900)/4)),

    //If it is not, then calculate the running total of days up to the month value

    IIF([Month]=1,0,IIF([Month]=2,31,IIF([Month]=3,59,IIF([Month]=4,90,IIF([Month]=5,120,IIF([Month]=6,151,IIF([Month]=7,181,IIF([Month]=8,212,IIF([Month]=9,243,IIF([Month]=10,273,IIF([Month]=11,304,334))))))))))),

    //If it is, then calculate the running total of days up to the month value (this is the same as above but with an added day from February)

    IIF([Month]=1,0,IIF([Month]=2,31,IIF([Month]=3,60,IIF([Month]=4,91,IIF([Month]=5,121,IIF([Month]=6,152,IIF([Month]=7,182,IIF([Month]=8,213,IIF([Month]=9,244,IIF([Month]=10,274,IIF([Month]=11,305,335))))))))))))

    //In excel add 1 to your date to make it the 1st of your month/year value. Due to differences in structure between Microsoft serial system and Tableau serial system (First of all, Tableau actually knows that the 29/02/1900 is not an actual date (LOL Microsoft), secondly the serial in Tableau for the first date in the system (01/01/1900) is 0, whereas as previously mentioned with the 1900 system, it is 1.)

    -1


    This calculation was really fun to problem solve and their were a number of complexities (primarily the result of leap years) that I had to work around in order to get it to work correctly. Hopefully the text included in the calculation documents it well enough to provide you with an understanding of how it works.

    I also learnt that what I had been tought at school in regards to leap years was wrong, it isn’t quite as simple as every 4 years.

    Leap_Centuries.jpg

    I was comforted (and I also found it hilarious) that Microsoft appear not to have known this fact when building the 1900 system where 29/02/1900 is acknowledged as a date, although Microsoft blame IBM for this and their users need to move between Excel and IBMs Lotus 1-2-3 spreadsheet programme.

    Peace out.

    Ben

Advertisements

One thought on “4 ways to prevent date conversion problems when building date strings

  1. Lovely post. Thank you. However I don’t think Microsoft should be laughed at. My understanding is that they knowingly included the 1900 bug because Lotus, their main competitor at the time, had the same bug. Fixing it would have lessened the interoperability with Lotus 1-2-3 spreadsheets.

    Like

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