Finding difference between two dates in Tableau in terms of days, rounded to two decimals

Question

I want to subtract two date fields in Tableau and find the difference in terms of days, rounded to two decimal places. I have already created a calculated field, however the result is rounded down to the nearest whole number.

The current calculated field I have is such:

DATEDIFF('day', [Reg Time], [Valid Time])

Which returns a result as such:

Reg Time           |   Valid Time          | Datediff
11/1/2018 12:00 AM    11/1/2018 1:00 PM          0

What I want is this:

Reg Time           |   Valid Time          | Datediff
11/1/2018 12:00 AM    11/1/2018 1:00 PM          .5

The datediff would return a result of 0.50 because the difference is 12 hours(half a day)

All help is greatly appreciated.

Answer 1

I assume you are working with fields whose data type is datetime instead of date. Otherwise, a result in whole number of days is as good as it is going to get :-)

Dates are compound data types, with internal structure. Every date has three component parts: a year, a month and day of the month. Date fields have a calendar icon beside the field name in the sidebar data pane.

Datetimes are also compound data types, and add three additional components: hour, minute and second. Datetimes add a little watch to the calendar symbol in their icons.

So if your data source truly has datetime data, and the Tableau datatype is set to datetime, then the following calculations get the effect you requested -- showing the difference or duration measured in units of days.

DATEDIFF('hour', [Reg Time], [Valid Time]) / 24

or

DATEDIFF('minute', [Reg Time], [Valid Time]) / (24 * 60)

This calculation is useful when making Gantt bars since the field on the size shelf in that case is assumed to be expressed in days.

Answer 2

The DATEDIFF function will work for you however, the 'day' date part is going to round up. Working around this use the 'hour' date part in your DATEDIFF function.

Then you'll want to divide the result of this calculation by 24 (hours in the day) to get the fraction of a day.

The last thing you need to do is make sure not to aggregate these values, which Tableau will try to do by default.

Hope this helps.