Formula to transform Minnutos into Days/Hours/Minutes/Seconds

Asked

Viewed 28 times

0

How can I make a formula to turn minutes into Days/Hours/Minutes/Seconds but it doesn’t leave the stake 0 I’ve tried several ways but I’m not getting it.

O valor em minutos é: 7188,15
=INT(D8/60/24) *isso me da o dia 4 dias inteiro
=(D9/60/24-E9)*24 *isso me da as 23,80 horas

The problem is that I would like to leave all values in the same cell as 4d 23h 50m 33s and I can’t find a formula to do that.

excel

1 answer

0


I was able to get to a formula using the formulas of QUOCIENTE calculating the entire division of a division, and the formula MOD which returns the rest of the entire division.

Day

To calculate the hours, we use the function QUOCIENTE with the time in minutes in the numerator and the value of 1440 (amount of minutes in 1 day) in the denominator, returning the entire division of the total minutes and the amount of minutes in a day:

=QUOCIENTE(A1;1440)

Time

To calculate the hours, we use the function again QUOCIENTE, where in the numerator goes the function MOD, returning the rest of the entire days division and the value 60 in the denominator, which is the amount of minutes in one hour:

=QUOCIENTE(MOD(A1;1440);60)

Minute

To calculate minutes, we calculate the entire value of the rest of the split between the rest of the days divided with the value 60:

=INT(MOD(MOD(A1;1440);60))

According to

Finally, to calculate the seconds, we use the total value of minutes minus the total integer value of minutes to obtain only the numbers after the decimal places, which are equivalent to the seconds, then just multiply by 60 seconds:

=INT((A1-INT(A1))*60)

After that you just put it all together.

The complete formula stays like this, where A1 is your cell where you will place the number in minutes to calculate the time.

=QUOCIENTE(A1;1440)&"d "&QUOCIENTE(MOD(A1;1440);60)&"h "&INT(MOD(MOD(A1;1440);60))&"m " &INT((A1-INT(A1))*60) & "s"

See more about the function MOD and QUOTIENT

  • Perfect! It worked great, and thank you for the detailed explanation.

Browser other questions tagged excel

You are not signed in. Login or sign up in order to post.