cancel
Showing results for
Did you mean:
Highlighted

## Calculate hours between two date/time column

Hi

I have a two calculated column
Col1 Col2
27-12-2019 04:35:00 28-12-2019 05:24:00

Need to calculate hours between col2-col1

Both column have date/time datatype

I need a total hours between the date.

21 REPLIES 21
Highlighted
Super User IX

## Re: Calculate hours between two date/time column

Column = DATEDIFF([Col1],[Col2],HOURS)

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted

## Re: Calculate hours between two date/time column

Hi
I tried but i have a date/time column
If i use that formula i am getting 0 for all rows

Is there any other way to calculate?

Thanks
Highlighted
Super User V

## Re: Calculate hours between two date/time column

Hi,

Try this calculated column formula

=Data[Col2]-Data[Col1]

Format this column as Date/Time.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted

## Re: Calculate hours between two date/time column

Hi
I need total hours between the two column

Thanks
Highlighted
Super User VI

## Re: Calculate hours between two date/time column

Do this in Power Query. Your model will perform better, and it is a simple formula.

Add a new Custom Column in Power Query and add this formula, then round as desired.

``= Duration.TotalHours([Col1] - [Col2])``

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Highlighted
Super User V

## Re: Calculate hours between two date/time column

Hi,

This calculated column formula works

=DATEDIFF([Col1],[Col2],MINUTE)/60

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted

## Re: Calculate hours between two date/time column

Hi @edhans

Thanks for the replay but I have done some calculation for getting (calculated column) date/time for the table. so i need dax to achieve this in front side.

To create column in query editor is difficult for me.

is there any way to achieve in dax?

Highlighted
Super User VI

## Re: Calculate hours between two date/time column

@Ashish_Mathur did it in DAX, which will work better for you since your dates were created in DAX. Consider using Power query in the future for all columns though.  It is just good practice. The problem is calculated columns are very "excel-like" and Excel users jump on them, but that is not a good long term practice, especially for large models.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

## Re: Calculate hours between two date/time column

i used

datediff =datediff(startate,enddate, hours)

@Ashish_Mathur  i tried this too

=DATEDIFF([Col1],[Col2],MINUTE)/60

i am getting 0

is there any problem in the format?