cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JWE Regular Visitor
Regular Visitor

Convert minutes into days and timeformat

Hi folks

 

I want to convert minutes into readable stuff for the endusers, e.g.:


I have 2.480,08 minutes

I want to format this in duration (but not in Time-Format) because the value is greater than 24 hours.

Result shoud be:

1 day 08:22:16

 

But without VAR - this slowdown extremly all my visualizations - isn´t it?

 

How to proceed?

 

Thanks in advance Jorg

1 ACCEPTED SOLUTION

Accepted Solutions
JWE Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Hi - thanks, it works. Hopefully the VAR function works quick enough also with big data!

 

I have modified your DAX, to display the days or not, and hour work day has only 15 hours, so I divide through 900 instead of 1440.

 

WE Dauer gesamt =
var Tag=  INT(Lagerbeleg[WE Dauer in Min] / 900)
var Stunde=  INT(MOD(Lagerbeleg[WE Dauer in Min]; 900) / 60)
var Minute=  MOD(MOD(Lagerbeleg[WE Dauer in Min]; 900); 60)
var Sekunde= INT((Lagerbeleg[WE Dauer in Min] - INT(Lagerbeleg[WE Dauer in Min])) * 60)
return
IF(Tag = 0; "";
  IF (Tag > 1; Tag & " Tage "; Tag & " Tag "))
& FORMAT(Stunde; "#00")
& ":"
& FORMAT(MINUTE; "#00")
&":"&
FORMAT(Sekunde; "#00")

10 REPLIES 10
Community Support Team
Community Support Team

Re: Convert minutes into days and timeformat

Hi @JWE,

 

You can try to use belwo formula if it works on your side:

 

Format =
var dayNo=INT([Minutes]/1440)
var hourNo=INT(MOD([Minutes],1440)/60)
var minuteNO=MOD(MOD([Minutes],1440),60)
var secondNo=INT(([Minutes]-INT([Minutes]))*60)
return
dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")

 

Capture.PNG

 

Regards,

Xiaoxin Sheng

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



For learning resources/Release notes, please visit: | |
JWE Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Hi - thanks, it works. Hopefully the VAR function works quick enough also with big data!

 

I have modified your DAX, to display the days or not, and hour work day has only 15 hours, so I divide through 900 instead of 1440.

 

WE Dauer gesamt =
var Tag=  INT(Lagerbeleg[WE Dauer in Min] / 900)
var Stunde=  INT(MOD(Lagerbeleg[WE Dauer in Min]; 900) / 60)
var Minute=  MOD(MOD(Lagerbeleg[WE Dauer in Min]; 900); 60)
var Sekunde= INT((Lagerbeleg[WE Dauer in Min] - INT(Lagerbeleg[WE Dauer in Min])) * 60)
return
IF(Tag = 0; "";
  IF (Tag > 1; Tag & " Tage "; Tag & " Tag "))
& FORMAT(Stunde; "#00")
& ":"
& FORMAT(MINUTE; "#00")
&":"&
FORMAT(Sekunde; "#00")

Re: Convert minutes into days and timeformat

Hello...Im Christián and I think I need something just like that...! my question is if  you are able to sum that column? eg: 4 days; 8:59+2 days:3:01 geting as a result= 6days:12:00?

JWE Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Hello

 

yes, after I calculate the sum (like above measure, or something else) - and then I used a second measure to display days and also hours.

 

WE Dauer (Zeit) =
IF (
    [Einl Dauer Sum] > 0;
    FORMAT ( INT ( [Einl Dauer Sum] / ( 1 / 24 * 15 ) ); "#00T " )
        & FORMAT ( MOD ( [Einl Dauer Sum]; ( 1 / 24 * 15 ) ); "HH:MMSmiley FrustratedS" )
)

 

(In this case I use 15 - because our working days are not 24h only 15h.)

Highlighted

Re: Convert minutes into days and timeformat

Hi @v-shex-msft, Could you tell me how this formula should be if I only want hours and minutes?

 

Id appreciate your help in advance!!!

 

Format =
var dayNo=INT([Minutes]/1440)
var hourNo=INT(MOD([Minutes],1440)/60)
var minuteNO=MOD(MOD([Minutes],1440),60)
var secondNo=INT(([Minutes]-INT([Minutes]))*60)
return
dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")

akhilduvvuru10 Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Hi @JWE,

 

The below mentioned DAX formula works for me as well. But when I tried to add two rows,

DW.JPG

I'm not getting the desired result because the value is taking as First or Last or Count but not Sum as mentioned below.

 

P.S: I'm using Impala as my Datasource.

 

D.JPG

Can you please help me to get this?

 

Thanks,

Akhil.

JWE Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Hi Akhil

 

sorry but I have no idea. I only work with the DAX in the measure and display the results without using further field functions. In this case I get the correct sum.

 

Cheers Jorg

akhilduvvuru10 Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Hi @JWE,

 

Thanks for your response!

 

Can I have the same formula work with the measure instead of creating a new column using the below DAX?

 

Time_Format = 

var dayNo=INT([time]/1440)
var hourNo=INT(MOD([time],1440)/60)
var minuteNO=MOD(MOD([time],1440),60)
var secondNo=INT(([lingertime]-INT([time]))*60)
return
dayNo&" day "&FORMAT(hourNo,"#00")&":"&FORMAT(minuteNO,"#00")&":"&FORMAT(secondNo,"#00")

 

Thanks,

Akhil.

JWE Regular Visitor
Regular Visitor

Re: Convert minutes into days and timeformat

Yes, I think so, but you know measure is not the same like calculatd column.

 

Below measure I used:

 

WE Dauer gesamt =
var Tag=  INT(   Lagerbeleg[Hilfsmeasure WE Dauer in Min] / 900)
var Stunde=  INT(MOD(  Lagerbeleg[Hilfsmeasure WE Dauer in Min] ; 900) / 60)
var Minuten= INT(MOD(MOD( Lagerbeleg[Hilfsmeasure WE Dauer in Min] ; 900); 60))
var Sekunde= INT((   Lagerbeleg[Hilfsmeasure WE Dauer in Min]
    - INT(   Lagerbeleg[Hilfsmeasure WE Dauer in Min] )) * 60)
return
IF(Tag = 0; "";
  IF (Tag > 1; Tag & " Tage "; Tag & " Tag "))
& FORMAT(Stunde; "#00")
& ":"
& FORMAT(Minuten; "#00")

 

inside this I used:

Hilfsmeasure WE Dauer in Min =
SUMX(FILTER(
 Lagerbeleg;
    Lagerbeleg[Startdatum] = Lagerbeleg[Endedatum]
 && Lagerbeleg[Endezeit] = Lagerbeleg[MaxEndeZeit]
 && Lagerbeleg[LagerPosNr] = 1);
  Lagerbeleg[Yakar Dauer Min gesamt])
    + (SUM(Lagerbeleg[Hilfsspalte WE > 1 T Sum]))
&":"&
FORMAT(Sekunde; "#00")

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 115 members 1,648 guests
Please welcome our newest community members: