cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wrivers626 Frequent Visitor
Frequent Visitor

DATEDIFF and True Number of Hours Returned

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: DATEDIFF and True Number of Hours Returned

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

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

4 REPLIES 4
Phil_Seamark Super Contributor
Super Contributor

Re: DATEDIFF and True Number of Hours Returned

HI @wrivers626

 

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

 

Please try this

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

wrivers626 Frequent Visitor
Frequent Visitor

Re: DATEDIFF and True Number of Hours Returned

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 Super Contributor
Super Contributor

Re: DATEDIFF and True Number of Hours Returned

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

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

wrivers626 Frequent Visitor
Frequent Visitor

Re: DATEDIFF and True Number of Hours Returned

@Phil_Seamark that worked!  Thanks so much!!!