DATEDIFF and True Number of Hours Returned

[wrivers626]

Hello everyone,

I was hoping someone could help me.  I have searched the posts with regard to using DATEDIFF but I haven't found anything that can help me solve this issue.

I have two columns that are in datetime format: 1/01/2018 00:00:00 AM and I have created a calculated column Approval to SchedStart that shows the hours difference between the two datetimes.

Column A is the start date and column B is the end date and I am using HOUR as the interval so it looks like this:
Approval to SchedStart = DATEDIFF('Table'[ColumnA].[Day], 'Table'[ColumnB].[Date],HOUR)

The problem I am running into the value of Approval to SchedStart is being rounded instead of showing what the actual number of hours between the two datetime fields are.  So for instance, I only get 0,24,48, 72 etc., instead of the actual hours like 5,9,22.5 etc.  Does anyone know how to get that actual number of hours?  Thanks!

[Phil_Seamark]

HI @wrivers626

Please remove the .[Date] and .[Day] off your column references.

Please try this

Approval to SchedStart = DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR )

[wrivers626]

Hi @Phil_Seamark!  Sorry for the late reply.  I was traveling and hadn't had a chance to check emails.  I did try to remove the .[Date] and .[Day].  However, when I did that the error "In DATEDIFF function, the start date cannot be greater than the end date"  I know that this is not the case. 

The "Start Date" which is column A is always an "Approval Date" and the "End Date" which is column B is always a "Scheduled to Start" date.  So first there has to be an approval (Column A) which in turn creates a scheduled to start date (Column B).  See below.  Thanks!

Change Approval Date           Change Scheduled Start Date                Difference In Hours

10/12/2017 2:31:41 PM              10/13/2017 10:00 AM                     Should give total hours diff

[Phil_Seamark · Accepted solution]

You could try this quick and dirty trick to resolve that error

Approval to SchedStart  = IFERROR( DATEDIFF('Table'[ColumnA], 'Table'[ColumnB], HOUR ),-999)

[wrivers626]

@Phil_Seamark that worked!  Thanks so much!!!