How to represent the datediff result as DD HH MM

[coejnot]

Hello,

I would like to represent the difference between two dates with days hours and minutes like this:

21 D 2 h 30 m

Is there any formula for this?

I have used the datediff for finding the difference, but I need to choose between days, hours, min etc.

Than you!

[Anonymous]

Hello @coejnot 

Hope this will resolve your query.

Following is the DAX Query:

DDHHMM =

VAR R =

MOD('Table'[Minutes],1440)

VAR DD =

INT(DIVIDE('Table'[Minutes],1440))

VAR HH =

INT(DIVIDE(R,60))

VAR MM =

MOD(R,60)

RETURN

DD&"D "&HH&"H "&MM&"M "

Thank you!

[v-robertq-msft · Accepted solution]

Hi, @coejnot 

Have you found the solution to this problem now?

According to your description, I can clearly understand your requirement, you can try to create two calculated columns like this to achieve your requirement:

This is my test data:

Datediff = DATEDIFF([Start date],[End date],MINUTE)

Output =

var dayNo=INT([Datediff]/1440)

var hourNo=INT(MOD([Datediff],1440)/60)

var minuteNO=MOD(MOD([Datediff],1440),60)

var secondNo=INT(([Datediff]-INT([Datediff]))*60)

return

dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")

And you can get what you want, like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[coejnot]

I found another thread about this:

https://community.powerbi.com/t5/Desktop/Convert-minutes-into-days-and-timeformat/m-p/102994

I will try this out. 🙂

[goncalogeraldes]

Hello there @coejnot ! You could use the FORMAT() function like so:

Date_Format = FORMAT('Date'[Date diff measure],"DD\D hh\h mm\m")

Hope this answer solves your problem!
If you need any additional help please @ me in your reply.
If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
Thanks!

You can also check out my LinkedIn!

Best regards,
Gonçalo Geraldes

[coejnot]

Thank you!

It is almost answering my issue. Now the format is correct, but there is no data in the hh and mm. 

I guess I acutually need to ask the question in a different way. 

How to convert a number of minutes to number of days, hh and minutes?

1200 min -> how many dd hh mm is this? In this format?