TransWikia.com

A Google Sheets format that returns time durations in days, hours, and minutes

Web Applications Asked by Matthew Daly on December 11, 2021

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?

One Answer

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

=INT(A1)&"d "&TEXT(A1,"hh mm")

Answered by Erik Tyler on December 11, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP