Display correct time values

In LibreOffice Calc I created a new sheet with in CELL A1 containing the number 6154 as plain text. This number represents the total amount of minutes.

What I want to achieve is to display this in CELL B1 as “4 days 06:34”. This should be a simple formula but I can’t seem to get it work.

What I have now in B1 is this formula: =6154/1440 and the CELL Format set to [HH]:MM
The output on my sheet displays: 06:34 which is total ridiculous because obviously incorrect value but after some research I found out that I had to put brackets on it like this [HH]:MM to display correctly as 102:34

Now the only step I am struggling with and can’t seem to find an answer to is how to convert this to displaying as “4 days 06:34”

I tried to change Format Cell to d" Days “uu”:"mm which seemed to work in Excel but in LibreOffice Calc it shows “3 days 06:34”

Can someone please help me with this because it’s driving me nuts!

Please consider to use SI conforming units (correctly abbreviated and separated). For durations we shouldn’t use the colon-separated notations. They are only specified for TOD (TimeOfDay) which is a quite different thing.
In the given case the formula given by @JohnSUN can be modified to =TEXT(INT(A1/1440);"0"" d """) & TEXT(MOD(A1/1440;1);"h"" h ""m"" min""") to get it the way I mean.
=INT(A1/1440) & " d " & INT(MOD(A1;1440)/60) & " h " & MOD(A1;60) & “min” is a slightly shorter alternative not needing the TEXT() function.

2 Likes

Welcome! Try this =INT(A1/1440) & " day(s) " & TEXT(A1/1440;"HH:MM")

3 Likes

Thank you very much for your quick solution. It worked!
I was not familiar with the “INT” function.

1 Like