Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
HenryJS
Post Prodigy
Post Prodigy

Calculating Time from Start & End Times

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

 

time3.PNG

2 ACCEPTED SOLUTIONS
v-eachen-msft
Community Support
Community Support

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.

 2-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

v-eachen-msft
Community Support
Community Support

Hi @HenryJS ,

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.

View solution in original post

16 REPLIES 16
v-eachen-msft
Community Support
Community Support

Hi @HenryJS ,

 

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

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
v-eachen-msft
Community Support
Community Support

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.

 2-1.PNG

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
parry2k
Super User
Super User

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

time4.PNG

@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...

@amitchandak @parry2k how do I get the time?

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

@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?

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k please see below, the whole number is incorrect

 

 

 

 

time5.PNG

@HenryJS what suppose to be the number?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@HenryJS what is the data type of these columns?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

@parry2k 'time'

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.