Calculating Time from Start & End Times

[HenryJS]

Hi all,

How can I calculate the hours between two start / end times? The below formula does not calculate the hours correcly

Engage Hours per day = ([Start]-[End])

[v-eachen-msft · Accepted solution]

Hi @HenryJS ,

You could use Time.Hour() in query editor.

if [Start]>[End] then
24-Time.Hour([Start])+Time.Hour([End])
else Time.Hour([End])-Time.Hour([Start])

Here is the result.

 

[v-eachen-msft · Accepted solution]

Hi @HenryJS ,

Does that make sense? If so kindly mark my answer as a solution to close the case. Thanks in advance.

[v-eachen-msft · Accepted solution]

Hi @HenryJS ,

You could use Time.Hour() in query editor.

if [Start]>[End] then
24-Time.Hour([Start])+Time.Hour([End])
else Time.Hour([End])-Time.Hour([Start])

Here is the result.

 

[parry2k]

@HenryJS try this expression

Column = DATEDIFF ( [Column1], [Column2], HOUR)

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[HenryJS]

@parry2k unfortunately that didn't work, please see below

[amitchandak]

@HenryJS , The formula of @parry2k  will give the whole number. Not time.

You can do like

time([Date diff],0,0)

or

https://radacad.com/calculate-duration-in-days-hours-minutes-and-seconds-dynamically-in-power-bi-usi...

[HenryJS]

@amitchandak @parry2k how do I get the time?

[amitchandak]

@HenryJS , That is what your initial formula was doing ? was it a measure or column?

[HenryJS]

@amitchandak @parry2k I have two columns 'Start' and 'End' both 'Time' data type.

I need a new column which calculates the hours difference between the start and end times.

Is this possible?

[parry2k]

@HenryJS we already gave you a solution, why you are asking the same question. Can you add a new column based on the expression I posted.

Your existing column has a time format because your original calculation was [Start] - [End] which returned time.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[HenryJS]

@parry2k please see below, the whole number is incorrect

[parry2k]

@HenryJS what suppose to be the number?

[HenryJS]

@parry2k  20:00pm - 05:00am should be a duration of 09:00 hours

[parry2k]

@HenryJS you have to add a date identifier to make it happen because simple time doesn't know if it is the same date or future date.

[parry2k]

@HenryJS you should keep the type of this new column to be a whole number, not time and I guess that is the issue. And as a best practice, always use table name with the columns in the DAX expression, not just column name and for measures never use the table name.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[parry2k]

@HenryJS what is the data type of these columns?

[HenryJS]

@parry2k 'time'