Google Sheets Formatting – Return Time Durations in Days, Hours, and Minutes

formattinggoogle sheets

I have a formula in a cell that returns the duration of a certain task in days. I'd like to express it in terms of days, hours, and minutes. (For instance, 3.25 might be written like 3 6:00.) I naively thought that the custom format d mm:hh would fit the bill. But d gives the day of the month, and unfortunately 0 days is expressed as 30, 1 as 31, 2 as 1, and so on.

So, like a good geek, I looked it up on the Google API guide. I looks like what I really want is a [d] format for the number of days in a duration, but Google hasn't implemented that. At the same time, I find it hard to believe that I'm the first person who has wanted to do this. Does anyone have an idea for a workaround?

Best Answer

Matthew, if your decimal number were in A1, try this:

=INT(A1)&"d "&TEXT(A1,"h\h m\m")