1

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?

Improve this question

1 Answer 1

Reset to default
1

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

=INT(A1)&"d "&TEXT(A1,"h\h m\m")
Improve this answer
2
  • Yeah, that works well! Pity I need to have one cell for the calculation and another for the display, but I can make it work.
    – Matthew Daly
    Commented Jul 23, 2020 at 15:07
  • 1
    Who says you need to have a separate cell for the calculation? You likely don't. You'd just put the calculation in place of the A1 in both places in the formula. It's always fastest, most accurate and easiest all around if you share a link to a sample sheet (with permission set to "Anyone with the link can edit") that clearly shows what you're trying to do. For instance, you likely only need one formula to do all the calculations for an entire column and return the formatted results for that column, no dragging. But there's no way to know without seeing what you're working with.
    – Erik Tyler
    Commented Jul 23, 2020 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.