4

In Tableau Desktop, I've got two date dimensions representing the begin date and the end date of an event. To know the duration of an event, I do DATEDIFF('second', [begin date], [end date]).

Unfortunately, some events last less than one second so there are many zero length durations.

How can I get more precision (milliseconds would be fine)?

My data source is an Excel file.

Benoît Guédas
  • 801
  • 7
  • 25
  • what is the format of your date dimension? I solved a similar issue by splitting the date and calculating `[minutes] * 60 + [seconds] + [Milliseconds]` so you might be able to adapt that, and use a normal DATEDIFF() plus the seperately calculated milliseconds – Alexander Dec 02 '15 at 00:16
  • @Alexander the format is an Excel timestamp in the source file (decimal number), and interpreted as "Date & time" by Tableau. By splitting the date, you mean adding a column with millis as integer in the source? – Benoît Guédas Dec 02 '15 at 09:29
  • I think another solution would be to read the dates as decimal numbers (Excel timestamps) and convert them by hand. – Benoît Guédas Dec 02 '15 at 09:32
  • Well if your timestamp is in seconds you obviously won't be able to calculate the milliseconds. Can you post an example of the timestamp? – Alexander Dec 02 '15 at 09:39
  • @Alexander actually, Excel stores dates as numeric values where the integer part is the number of days from January 1, 1900, and the decimal part is the time (fraction of day). For instance, I've got this : 42223,5986939005 which looks precise enough. – Benoît Guédas Dec 02 '15 at 09:56

1 Answers1

5

Try the following:

  1. define the column as a float
  2. subtract the float dates as you need to in a calculated field
  3. now you have the difference as a decimal in days, if you want to get it down to milliseconds, you do [floatdate] * 24 * 60 * 60

If you need the column in a proper date format as well, you can just copy it with a calcualted field in another column and define that as a date.

Alexander
  • 1,969
  • 19
  • 29