1 Reply Latest reply on Jan 7, 2013 4:13 AM by Robin Kennedy

    Better DATE function (feature request?)

    Neil Charles

      I've solved this issue in a roundabout way, but would appreciate any better ideas! Tableau could really do with a DATE() function that works like the Excel DATE() function, rather than the current one which works like Excel's DATEVALUE() function.

       

      If you read a string as a date, say today's date of DATE("07/01/2013"), Tableau will try to read it in a US format as MM/DD/YYY, I.E. the 1st July 2013. I'm in the UK, so I meant DD/MM/YYYY. There seems to be some inconsistency between Desktop and Server and different desktops as to exactly what Tableau does - I haven't worked it out anyway - but it's caused us problems a few times. One workbook even worked fine on my PC and then gave wrong data to a user in server. I can definitely say that Tableau has switched dates to a MM/DD/YYY format when I didn't want it to.

       

      One solution (for anyone searching) is to construct a date string that Tableau can't switch, say: "25/01/2013", use that to pull out the month name and then rebuild the original date. This works:

       

      For a variable of: MyDate = "07/01/2013"

       

      DATE(LEFT(MyDate,2) + "-" + DATENAME('month',"25-" + MID(MyDate,4,2) + "-" + RIGHT(MyDate,4)) + "-" + RIGHT(MyDate,4))

       

      But it's not exactly clean!

       

      Excel's DATE(year,month,day) function would help a lot as you could break up the date string yourself without needing the month name trick. Even if this one can be resolved by getting local settings right, it would be much clearer to have a formula to do it.

        • 1. Re: Better DATE function (feature request?)
          Robin Kennedy

          I find that the DATEADD function usually does the trick for these sorts of date field manipulations as you can specify which part of the date you mean but agree that a function similar to Excel's DATE would be slicker.

           

          However, if I found that my date field was being read as a string I would probably go back to the datasource to sort that out there rather than do a load of string manipulation in Tableau as it can really slow calculations down if you're working with large data sets.

          1 of 1 people found this helpful