cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate II
Advocate II

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.

Thanks in advance
21 REPLIES 21
Highlighted
Super User IV
Super User IV

Re: Calculate hours between two date/time column

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

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

Putting square pegs in round holes since 1972.

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Advocate II
Advocate II

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 IV
Super User IV

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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Advocate II
Advocate II

Re: Calculate hours between two date/time column

Hi
I need total hours between the two column

Thanks
Highlighted
Super User II
Super User II

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])

 

2020-03-25 18_56_31-Table2 - Power Query Editor.png 



Did I answer your question? Mark my post as a solution!
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 IV
Super User IV

Re: Calculate hours between two date/time column

Hi,

This calculated column formula works

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Advocate II
Advocate II

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 II
Super User II

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 I answer your question? Mark my post as a solution!
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
Advocate II
Advocate II

Re: Calculate hours between two date/time column

date diff.PNG

@Ashish_Mathur @edhans @Greg_Deckler 

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?

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors